Select sum where clause SQL

Question

I have the following code, it works but I am trying to separate SUM for each Banksphere.servicio_id column, this code SUM only one servicio_id... I'm a bit lost, can someone help me?

As you can see, every WHERE clause is exactly the same but Banksphere.peticion_id which is the only one that changes... So maybe there's some better way just to filter once the common clauses and leave only peticion_id for OK and KO?

SELECT
(SELECT
    SUM(valor)
FROM
    Banksphere
WHERE
    Banksphere.fecha = '2013-01-14'
AND
    Banksphere.servicio_id = '6'
AND
    Banksphere.entidad_id = '2'
AND
    Banksphere.peticion_id = '0') AS OK,
(SELECT
    SUM(valor)
FROM
    Banksphere
WHERE
    Banksphere.fecha = '2013-01-14'
AND
    Banksphere.servicio_id = '6'
AND
    Banksphere.entidad_id = '2'
AND
    Banksphere.peticion_id = '1') AS KO

EDIT WITH WORKING CODE

SELECT  Servicios.nombre as servicio,
        SUM(case when peticion_id = '0' then valor end) as OK,
        SUM(case when peticion_id = '1' then valor end) as KO
FROM    Banksphere
INNER JOIN
    Servicios
ON
    Banksphere.servicio_id = Servicios.id
WHERE   Banksphere.fecha = '2013-01-14'
        AND Banksphere.entidad_id = '2'
        AND Banksphere.peticion_id in ('0', '1')
group by Servicios.nombre

score 29 · Accepted Answer · answered Jan 16 '13 at 14:42

I think you want something along these lines:

SELECT  banksphere.servicio_id, SUM(valor),
        SUM(case when peticion_id = '0' then valor end) as OK,
        SUM(case when peticion_id = '1' then valor end) as KO
FROM    Banksphere
WHERE   Banksphere.fecha = '2013-01-14'
        AND Banksphere.entidad_id = '2'
        AND Banksphere.peticion_id in ('0', '1', ...)
group by banksphere.servicio_id

This has a group by so you can get multiple "servicio_ids" and it adds separate columns for OK and KO. If you want only servicio_id = 6, then add that back into the where clause. And, you might want other variables in the group by as well, but you only mention service in the question.

score 4 · Answer 2 · answered Jan 16 '13 at 14:41

SELECT  servicio_id,
        entidad_id,
        SUM(CASE WHEN peticion_id = 0 THEN valor ELSE 0 END) OK,
        SUM(CASE WHEN peticion_id = 1 THEN valor ELSE 0 END) KO
FROM    BankSpehere
WHERE   fecha = '2013-01-14' AND 
        entidad_id = '2' AND 
        peticion_id in ('0', '1')
GROUP BY servicio_id, entidad_id

score 2 · Answer 3 · answered Jan 16 '13 at 14:39

2

SELECT  SUM(valor)
FROM    Banksphere
WHERE   Banksphere.fecha = '2013-01-14'
        AND Banksphere.servicio_id = '6'
        AND Banksphere.entidad_id = '2'
        AND Banksphere.peticion_id in ('0', '1', ...)

answered Jan 16 '13 at 14:39

Andomar

232,371
49
380
404

Thank you Andomar it seems to work as I wanted... I must learn to use "IN" more often.. Thanks – Robert W. Hunter Jan 16 '13 at 14:42
Shouldn't there be a `GROUP BY` with `SELECT peticion_id, SUM(valor)`? – Kermit Jan 16 '13 at 14:42

Select sum where clause SQL

3 Answers3