CODEWITHSUNDEEP
mysqlaggregate-functions
Anthony
Anthony

Reputation: 37065

Select SUM of rows with different WHERE condition per column

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?

Upvotes: 2

Views: 1738

Answers (1)

Alma Do
Alma Do

Reputation: 37365

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

Upvotes: 3

Related Questions