选择 sum where 子句 SQL

Select sum where clause SQL

提问by Robert W. Hunter

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?

我有以下代码，它可以工作，但我试图为每个 Banksphere.servicio_id 列分隔 SUM，此代码 SUM 只有一个 servicio_id...我有点迷茫，有人可以帮助我吗？

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?

正如您所看到的，每个 WHERE 子句都完全相同，但 Banksphere.peticion_id 是唯一一个改变的......所以也许有更好的方法来过滤一次公共子句并只留下 peticion_id 用于 OK 和 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

回答by Gordon Linoff

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 byso 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 whereclause. And, you might want other variables in the group byas well, but you only mention service in the question.

这有一个，group by因此您可以获得多个“servicio_ids”，并为 OK 和 KO 添加单独的列。如果您只想要 servicio_id = 6，则将其重新添加到where子句中。而且，您可能还需要其他变量group by，但您只在问题中提到了服务。

回答by John Woo

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

回答by Andomar

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', ...)