SQL - how to add a value with a condition to a selection?-CodePudding

I have the following table structure:

name	value	success
name 1	10	0
name 2	20	0
name 2	30	1

And my query is:

SELECT name, SUM(value) as valueTotal FROM TableName GROUP BY name

The result is:

name	valueTotal
name 1	10
name 2	50

Now I want to add a new column which will contain the sum of only successful rows. But if I add this condition, it will apply to all selected fields:

SELECT name, SUM(value) as valueTotal, SUM(value) as successValueTotal FROM TableName WHERE success = 1 GROUP BY name

Here is the result I want to get:

name	valueTotal	successValueTotal
name 1	10	0
name 2	50	30

How can I add a field with a separate condition that does not affect the main query? Thx)

CodePudding user response：

You can use the SUM function with a conditional aggregation on whether success is 1 or not. When success is 1, then take the value of the value field, otherwise sum up 0.

SELECT name, 
       SUM(value)                     AS valueTotal,
       SUM(IF(success = 1, value, 0)) AS successValueTotal
FROM TableName 
GROUP BY name

Try it here.

CodePudding user response：

This is the typical use case for CASE WHEN:

SELECT name, 
SUM(value) AS valueTotal,
SUM(CASE WHEN success = 1 THEN value ELSE 0 END) AS successValueTotal
FROM TableName 
GROUP BY name

You can (like lemon showed) also use an if clause in MYSQL. This is a bit shorter, but the query will not work on every DB while CASE WHEN does. So I think both is fine.