1

I'm new to SQL and I feel like that this must be something easy but cannot figure it out

I have this SQL query using MYSQL

SELECT incomeBand, 
case workLevel when "Casework" then round(COUNT(casenote) / COUNT(DISTINCT memberNumber),0) END, 
case workLevel when "Detailed" then round(COUNT(casenote) / COUNT(DISTINCT memberNumber),0) END, 
case workLevel when "Discrete" then round(COUNT(casenote) / COUNT(DISTINCT memberNumber),0) END, 
case workLevel when "Information" then round(COUNT(casenote) / COUNT(DISTINCT memberNumber),0) END 
FROM xxx-xxx.xxx.table 
WHERE date >= "2020-07-01" AND date <= 2020-09-30" AND funderMatch = "funderx"  
GROUP BY incomeBand, workLevel 
ORDER BY incomeBand

it outputs

band1   null   13   null  null
band1   34    null  null  null
band1   null  null   45   null
band1   null  null  null   63
band2   73    null  null  null
band2   null   12   null  null
band2   null  null   72   null
band2   null  null  null   42

but I want

band1   34    13   45   63
band2   73    12   72   42

How do I roll up the rows to the income band?

thanks

MrPea
  • 635
  • 1
  • 6
  • 22

1 Answers1

1

Take your query and make it a subquery:

SElECT incomeBand, MAX(col1), MAX(col2), MAX(col3), MAX(col4)
FROM (
 ...your query...
) AS t
GROUP BY incomeBand;

Many aggregate queries like MAX() ignore nulls, so in this example it will return the single non-null value in each group.

Tip: You should give column aliases to your four CASE expressions.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • wonderful. I don't really know why that works but it did! thanks – MrPea Oct 05 '20 at 23:41
  • 1
    GROUP BY reduces the result to one row for each distinct value in the column(s) you name in your GROUP BY clause. The rest of the columns must be in some aggregate expressions, and MAX() is one of those. – Bill Karwin Oct 05 '20 at 23:42