CODEWITHSUNDEEP
mysqlsqlcase
KriKox
KriKox

Reputation: 43

MySQL query with multiple conditions without WHERE

I would like to query a database on several variables with different data types.

My query looks like:

SELECT var1, var2,  
       SUM( CASE WHEN var3 ='2008' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS 'A1_U18_7',
       SUM( CASE WHEN var3='2015' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS 'A34_U15_0',
       CASE WHEN var3 = '2015' AND var4 = 'A0_RISE' THEN var5 ELSE 0 END AS 'A0_RISE'
FROM dataset1
GROUP BY var1

Actually I don't need to sum up the result of the first two queries, but otherwise I would have got a "0" as an output. With the sum I got the right answer.

However, this workaround is unpracticable in the case of "A0_RISE" because the data type is text. Without the SUM function I got here the output "0", too.

How could I get the output right for all variables? If you have a better idea than the SUM-workaround for doubles, I would be very appreciated to here about that!

Upvotes: 0

Views: 84

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Perhaps group_concat()?

SELECT var1, 
       SUM(CASE WHEN var3 ='2008' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS A1_U18_7,
       SUM( CASE WHEN var3='2015' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS A34_U15_0,
       GROUP_CONCAT(CASE WHEN var3 = '2015' AND var4 = 'A0_RISE' THEN var5 END) AS A0_RISE
FROM dataset1
GROUP BY var1;

Do not include var2 in the SELECT, unless you also include it in the GROUP BY.

Upvotes: 0

Ian Kenney
Ian Kenney

Reputation: 6426

Maybe use Max ?

SELECT var1, var2,  
       SUM( CASE WHEN var3 ='2008' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS 'A1_U18_7',
       SUM( CASE WHEN var3='2015' AND var4='A1_U18' THEN var5 ELSE 0 END ) AS 'A34_U15_0',
       MAX(CASE WHEN var3 = '2015' AND var4 = 'A0_RISE' THEN var5 ELSE '' END ) AS 'A0_RISE'
FROM dataset1
GROUP BY var1

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

cast the var5 to integer like this CAST(var5 AS UNSIGNED) and add var2 in group by like group by var1,var2

SELECT var1, var2,  
       SUM( CASE WHEN var3 ='2008' AND var4='A1_U18' THEN CAST(var5 AS UNSIGNED) ELSE 0 END ) AS 'A1_U18_7',
       SUM( CASE WHEN var3='2015' AND var4='A1_U18' THEN CAST(var5 AS UNSIGNED) ELSE 0 END ) AS 'A34_U15_0',
       CASE WHEN var3 = '2015' AND var4 = 'A0_RISE' THEN CAST(var5 AS UNSIGNED) ELSE 0 END AS 'A0_RISE'
FROM dataset1
GROUP BY var1,var2

Upvotes: 0

Related Questions