StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server case sum query
text
Body
copied!<p><strong>UPDATE</strong> <em>I've managed to get 1 or 0 depending on the count, but it is not separated by "Servicio" column so everything has 1 or 0 if there is any row matching...</em></p> <p>I have the following query which is not working... It never stops executing, what I want is to add 0 or 1 to each "Servicios" column...</p> <p>I have three tables, "Banksphere", "PAS" and "CAM" with relationship with "Entidades" and "Servicios"</p> <p>PAS and CAM does not have "servicio_id" but I fix that using the "Join" for Servicios... That's OK "Servicio" for PAS is 3, and CAM is 0.</p> <p>I hope I've explained it good... I can't do better now, I'm not on my PC</p> <p>Example:</p> <pre><code>Banksphere [ id | entidad_id | servicio_id | reconocido ] [ 1 | 3 | 9 | 0 ] [ 2 | 1 | 1 | 1 ] [ 3 | 4 | 6 | 0 ] PAS [ id | entidad_id | reconocido ] [ 1 | 3 | 0 ] [ 2 | 5 | 1 ] [ 3 | 4 | 0 ] CAM [ id | entidad_id | reconocido ] [ 1 | 0 | 1 ] [ 2 | 0 | 1 ] [ 3 | 0 | 1 ] </code></pre> <p>The resulset I want is this one....</p> <pre><code>[ Entidad | Servicio | Alertas ] [ 0 | 0 | 1 ] [ 1 | 1 | 1 ] [ 3 | 9 | 0 ] [ 3 | 3 | 0 ] [ 4 | 6 | 0 ] [ 4 | 3 | 0 ] [ 5 | 3 | 1 ] </code></pre> <p>Can someone help me? Thank you very much...</p> <pre><code>SELECT DISTINCT entidad, value, alertas FROM (SELECT Entidades.id AS entidad, Servicios.nombre AS servicio1, sp.nombre AS servicio2, sc.nombre AS servicio3, CASE WHEN ( CASE WHEN (SELECT COUNT(Banksphere.reconocido) FROM Banksphere WHERE Banksphere.reconocido = '0' AND Banksphere.fecha = '2012-12-18') = 0 THEN 0 ELSE 1 END + CASE WHEN (SELECT COUNT(PAS.reconocido) FROM PAS WHERE PAS.reconocido = '0' AND PAS.fecha = '2012-12-18') = 0 THEN 0 ELSE 1 END + CASE WHEN (SELECT COUNT(CAM.reconocido) FROM CAM WHERE CAM.reconocido = '0' AND CAM.fecha = '2012-12-18') = 0 THEN 0 ELSE 1 END) = 0 THEN 0 ELSE 1 END AS alertas FROM Entidades LEFT JOIN (Banksphere INNER JOIN Servicios ON ( Banksphere.servicio_id = Servicios.id )) ON Entidades.id = Banksphere.entidad_id AND Banksphere.fecha = '2012-12-18' LEFT JOIN (CAM INNER JOIN Servicios sc ON ( sc.nombre = 'CAM' )) ON Entidades.id = CAM.entidad_id AND CAM.fecha = '2012-12-18' LEFT JOIN (PAS INNER JOIN Servicios sp ON ( sp.nombre = 'PAS' )) ON Entidades.id = PAS.entidad_id GROUP BY Entidades.id, Entidades.nombre, Servicios.nombre, sp.nombre, sc.nombre, Banksphere.reconocido, PAS.reconocido, CAM.reconocido ) src UNPIVOT ( value FOR col IN (servicio1, servicio2, servicio3) ) unpiv GROUP BY alertas, entidad, value ORDER BY entidad ASC, value ASC </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload