Creating columns per where clause

Question

Is it possible to construct a query to create multiple columns based on multiple conditions and aggregate by time. For example a simple table like so:

id value created
1  45    datetime
2  52    datetime
3  24    datetime
4  33    datetime
5  20    datetime

I can get all values between 10 and 20 per week of the year like so:

SELECT count(*) as '10-20', week(created) as 'week', year(created) as 'year'
FROM table 
WHERE value > 10 AND value < 20
GROUP BY year(created), week(created)

This will give me for example:

10-20 week year
40    1    2014
21    2    2014
3     33   2014

I could repeat the query for ranges 20-30,30-40, 40-50 and manual join the outputs but I'd like a single queries combining these into a table, so the output would be:

10-20 20-30 30-40 40-50 week year
40    0     33    42    1    2014
21    1     0     2     1    2014
0     0     32    0     12   2014
3     42    34    32    33   2014

This is a "pivot", and examples abound. However, IMO a better idea is just to take a result set like you've got and handle the display logic at the application level (e.g. with a simple PHP loop) — Strawberry, Feb 26 '14 at 14:44
If you are getting, let's say just 3 options (10,20,30 weeks range) you can handle each with a 3 different queries. — jean, Feb 26 '14 at 14:45
I agree with @Strawberry, this is ideal case of PIVOT, but it can be achieved in the DB, so why bother the client side? — Ziouas, Feb 26 '14 at 14:47
Here is something that might help: http://stackoverflow.com/questions/14618316/how-to-create-a-pivot-query-in-sql-server-without-aggregate-function — Ziouas, Feb 26 '14 at 14:48
Ok, then you'll have to go extra mile for that effect as I briefly read it here: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Ziouas, Feb 26 '14 at 14:54
It's definitely more flexible and scalable (and probably faster) to do this client-side. — Strawberry, Feb 26 '14 at 14:56

score 1 · Accepted Answer · answered Feb 26 '14 at 15:24

You can use SUM instead of COUNT, with an IF inside the brackets of the sum

Something like this

SELECT SUM(IF(value > 10 AND value < 20), 1, 0) as '10-20', 
        SUM(IF(value > 20 AND value <  30), 1, 0) as '20-30', 
        SUM(IF(value > 30 AND value <  40), 1, 0) as '30-40', 
        SUM(IF(value > 40 AND value <  50), 1, 0) as '40-50',
week(created) as 'week', year(created) as 'year'
FROM table 
WHERE 
GROUP BY year(created), week(created)

Note that there is an issue here (and in your original code) for items with a value that is on the border (eg, if 20).

Flexibility wise you would probably be better off with another table that stores the ranges, join that to get get one row per range per week / year and then turn the rows to columns in your script. Saves amending the code when a range is added.

Creating columns per where clause

1 Answers1