Sum in a Select with where or IF SQL

Question

I created a SELECT and I want to 'SUM' different rows if the NIF are equal to an other nif and the contract are the same to other and the product is 100

SELECT 
 NIF, 
 CONTRACT, 
 PRODUCT, 
 SUM(SALDO) when PRODUCT=100 
 DATE
from TABLE_AAA

And then I would like to convert as a chart.

NIF     CONTRACT    PRODUCT     SALDO   DATE
1       1001        100           1     27/04/2016
1       1001        100           1     27/04/2016
1       1003        100           2     27/04/2016
1       1003        100           2     27/04/2016
1       1001        102           1     27/04/2016
2       1001        200           5     27/04/2016
2       1001        200           1     27/04/2016

NIF     CONTRACT    PRODUCT     SALDO   DATE
1       1001        100         2       27/04/2016
1       1003        100         4       27/04/2016
1       1001        102         1       27/04/2016
2       1001        200         5       27/04/2016
2       1001        200         1       27/04/2016

Add some sample table data and the expected result - as well formatted text. — jarlh, Apr 27 '17 at 07:59
Not as a comment, we can't read that. Edit your question instead, — jarlh, Apr 27 '17 at 08:40

Matt · Answer 1 · 2017-04-27T08:13:53.093

0

You need to use WHERE not when, and include your other parameter in the WHERE clause, also you need a GROUP BY. Without sample data and expected output this is what I presume you are after.

SELECT 
NIF, 
CONTRACT, 
PRODUCT, 
SUM(SALDO) AS SUMSALDO,
DATE
FROM TABLE_AAA
WHERE PRODUCT = 100
AND CONTRACT = NIF
GROUP BY NIF, CONTRACT, PRODUCT, DATE

Ok maybe you mean this:

SELECT 
NIF, 
CONTRACT, 
PRODUCT, 
SUM(CASE WHEN PRODUCT = 100 AND CONTRACT = NIF THEN SALDO ELSE 0 END) AS SUMSALDO,
DATE
FROM TABLE_AAA
GROUP BY NIF, CONTRACT, PRODUCT, DATE

edited Apr 27 '17 at 08:13

answered Apr 27 '17 at 08:02

Matt

14,906
27
99
149

@AleixCaminal added second answer – Matt Apr 27 '17 at 08:14

score 0 · Answer 2 · edited Apr 27 '17 at 08:23

0

This is the sum function of SQL. SELECT SUM(column_name) FROM table_name;

In your case,

SELECT NIF, CONTRACT, PRODUCT, SUM(SALDO) FROM Table_AAA WHERE PRODUCT = 100 AND NIF = CONTRACT;

I think this will help.

edited Apr 27 '17 at 08:23

Robert

5,278
43
65
115

answered Apr 27 '17 at 08:08

Anwesha Mukherjee

1

score 0 · Answer 3 · edited May 23 '17 at 11:54

You can try something like this:

SELECT 
NIF, 
CONTRACT, 
PRODUCT, 
CASE 
    WHEN (NIF = CONTRACT AND PRODUCT=100)
    THEN
        SUM(SALDO)
    WHEN(otherCondition)
    THEN
        SUM(otherSum)
    ELSE 
        NULL
END
DATE from TABLE_AAA

You can find more information about CASE in sql there How do I use properly CASE..WHEN in MySQL or there https://dev.mysql.com/doc/refman/5.7/en/case.html

score 0 · Answer 4 · answered Apr 27 '17 at 08:18

In case that you would like to sum, rows that satisfy the same condition and you have more than one condition, I think you can you the following query:

SELECT 
 NIF, 
 CONTRACT, 
 PRODUCT, 
 SUM( case when NIF=CONTRACT AND PRODUCT=100 THEN SALDO ELSE 0 END ) SUM_PROD_100 ,
 "DATE"
from TABLE_AAA
GROUP BY NIF, CONTRACT,PRODUCT, "DATE"

You can use the same logic as in column "SUM_PROD_100" to sum other rows that satisfy a different condition.

score 0 · Accepted Answer · answered Apr 27 '17 at 10:22

You want the combination of two queries actually:

the aggregated rows for product 100
the unaltered records for the other products

So write two queries and combine the two with UNION ALL:

select nif, contract, product, sum(saldo) as saldo, max(date) as date
from table_aaa
where product = 100
group by nif, contract, product
union all
select nif, contract, product, saldo, date
from table_aaa
where product <> 100
order by nif, contract, product, date;

Sum in a Select with where or IF SQL

5 Answers5