Home / Questions / 231441
Accepted
HM79
Asked: 2020-01-22 10:37:50 +0800 CST 2020-01-22 10:37:50 +0800 CST

Count Conditional

  • 772

I got stuck with a script, I show you what I have so far.

I have two tables, historial_numerosand historial_loterias I have to count the ones that have the column cod=1, the problem arises, that they can have different codes for each lottery.

Where I get stuck is that I need him to id_historial_numero =7mark only one row, not two, in the example I have, only the row with the

cod=1

In the last image I leave the query I have so far.

I thank you in advance for your help.

enter image description here

enter image description here

enter image description here

sql-server

1 Answers

  1. Best Answer

    Hello, if this is the expected result, you could consider using the query that I relate.

    Cheers

    enter image description here

    WITH cte
AS (SELECT
  id_historial_numero,
  COUNT(CASE
    WHEN cod = 1 THEN 1
  END) AS aciertos
FROM test.dbo.tbl_historial_loterias
GROUP BY id_historial_numero)
SELECT
  HL.id_historial_numero,
  HN.fecha,
  T.nombre AS turno,
  HN.numero,
  HL.aciertos
FROM cte AS HL
INNER JOIN test.dbo.tbl_historial_numeros AS HN
  ON HN.id = HL.id_historial_numero
INNER JOIN test.dbo.tbl_turnos AS T
  ON HN.orden_turno = T.orden;

    In this part of the query, it performed the COUNT and with this result it performed the join with the other tables.

    WITH cte
AS (SELECT
  id_historial_numero,
  COUNT(CASE
    WHEN cod = 1 THEN 1
  END) AS aciertos
FROM test.dbo.tbl_historial_loterias
GROUP BY id_historial_numero)

    enter image description here

    • 0