Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select sum where clause SQL

Tags:

sql

math

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
like image 558
Robert W. Hunter Avatar asked Jan 16 '13 14:01

Robert W. Hunter


People also ask

Can you put a SUM in a WHERE clause SQL?

In SQL, we use the SUM() function to add the numeric values in a column. It is an aggregate function in SQL. The aggregate function is used in conjunction with the WHERE clause to extract more information from the data.

How do you SUM SELECT in SQL?

If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.

Can we use WHERE clause with aggregate function?

An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.

Can you use SUM () in a GROUP BY SQL?

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.


1 Answers

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.

like image 152
Gordon Linoff Avatar answered Oct 15 '22 23:10

Gordon Linoff