SQL : SELECT SUM WHERE CONDITION

Question

I've got some troubles about SQL request : I have a table like this table data image

I would like to create a view from this table to get :

Time_A : SUM of a column (total_time_taken) WHERE column (is_radiant)=1

Time_B : SUM of the same column (total_time_taken) WHERE column (is_radiant)=0

Time_AB : SUM of the column (total_time_taken) WHERE column (is_radiant)=0 OR (is_radiant)=1

SELECT 
    SUM(`matchpickban_radiant`.`total_time_taken`) AS `draft_time_radiant`,
    SUM(`matchpickban_dire`.`total_time_taken`) AS `draft_time_radiant`
FROM
    (`matchpickban` AS `matchpickban_radiant`
    JOIN `matchpickban` AS `matchpickban_dire` ON ((`matchpickban_dire`.`idmatchpickban` = `matchpickban_radiant`.`idmatchpickban`)))
WHERE
    `matchpickban_radiant`.`is_radiant` = 1
    AND `matchpickban_dire`.`is_radiant` = 0

Actually I can run this request without syntax error but the result is NULL cause no data can be equal to 0 AND equal to 1 in the same time, obviously...

Also, I don't know if it's possible to make a JOIN the table to itself as I did (matchpickban JOIN matchpickban).

If syntax is correct I need to place my WHERE CONDITION away but don't know how, is it possible to replace it with 2 IF statement (IF is_radiant=0 SUM(...))

Thx for reading and helping me about this issue I got ! If you need more info about table or request I will give you all you need !

GMB · Accepted Answer · 2020-02-11 23:12:53Z

2

No need for a self-join or complex logic, you can just use conditional aggregation, which consists in using conditional expression within aggregate functions.

In MySQL, you could go:

select 
    sum(is_radiant * total_time_taken) time_a,
    sum((1 - is_radiant) * total_time_taken) time_b,
    sum(total_time_taken) time_ab
from matchpickban
where is_radiant in (0, 1)

This works because is_radiant is made of 0/1 values only - so this simplifies the logic. A more canonical way to phrase the conditional sums would be:

sum(case when is_radiant = 1 then total_time_taken else 0 end) time_a,
sum(case when is_radiant = 0 then total_time_taken else 0 end) time_b,

edited Feb 11, 2020 at 23:12

answered Feb 11, 2020 at 22:59

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MatBailie Over a year ago

Probably useful to also show an IIF/CASE version, and explain why your shortcut/simplification is probably fine.

GMB Over a year ago

@MatBailie: yes. I expanded my answer a little.

aut0wash Over a year ago

This 2 solutions worked fine for me ! Thx for the fast reply, and the explanations, I learned something !

Collectives™ on Stack Overflow

SQL : SELECT SUM WHERE CONDITION

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related