2

Either this is just not a good idea or it's so elementary that I'm not finding it. Suppose I have a table like:

 User  |  Q1  | Q2 |  Q3
 ann   |  3   |  2 |  5
 joe   |  1   |  4 |  4
 joe   |  5   |  2 |  2
 ann   |  4   |  4 |  4

What I would like is a single query that returns the sum of each column per user if the value for a specific column is greater than some number. So in the above example, the result would be:

 User  |  Q1  | Q2 |  Q3
 ann   |  7   |  4 |  9
 joe   |  5   |  4 |  4

If the per-column minimum is 3. But I know that if I try 

WHERE Q1 >= 3 AND Q2 >= 3 AND Q3 >= 3

I would't get row values in aggregate unless all 3 meet the minimum. While if I use

WHERE Q1 >= 3 OR Q2 >= 3 OR Q3 >= 3

I would get all the wanted rows, but would get values in aggregate below minimum.

Can this be done elegantly in one query?

Anthony
  • 36,459
  • 25
  • 97
  • 163

1 Answers1

3

So use IF:

SELECT 
  SUM(IF(Q1>=3, Q1, 0)) AS Sum_Q1,
  SUM(IF(Q2>=3, Q2, 0)) AS Sum_Q2,
  SUM(IF(Q3>=3, Q3, 0)) AS Sum_Q3,
  user
FROM
  t
GROUP BY
  user
Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • Would the same technique work for `AVG()` if I don't want the values below the threshold to be factored in (so AVG(5,0) = 5 not 2.5) – Anthony Nov 14 '13 at 07:17
  • NVM, AVG doesn't count `NULL` so I can substitute NULL for 0 from your example. Awesome. – Anthony Nov 14 '13 at 07:20
  • No, it will not work: for `SUM` you're adding zero, but you still are _handling_ row with value less than 3. That means - if we're talking about `AVG`, it will take all rows (so if we have values 3 and 5 - it will do: `(3 + 0)/2`). But that can be easily solved `with: SUM(IF(Q1>=3, Q1, 0))/COUNT(IF(Q1>=3, Q1, 0))`. Or you can use `NULL` for skip rows, yes (I mean, you can't use `0` for `AVG`) – Alma Do Nov 14 '13 at 07:21
  • It works with `AVG` using `NULL` instead of `0`, since MySQL leaves out both the value and the row when the column is NULL (so it's not counting towards sum or count). Check it : http://sqlfiddle.com/#!9/85db57/3 – Anthony Mar 21 '16 at 07:12