Query with Multiple Columns based upon same data 1

[DBrewsky]

I am looking to create a query that will tell me a number of results in multiple columns from a search of a single field.

Example: field name is 'timer' in the database, the values in the multiple columns are the total number of results within the search values

Thanks in advance!

--DB

 

[SkipVought]

Hi,

What you have is a non-normalized pivot REPORT: the worse thing to do a query on.

You need to query the table(s) that has 'timer' in that database.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ChrisHunt]

I don't have a MySQL database to hand to test this, but maybe something like this...

SELECT user_name,
       SUM(CASE WHEN timer <= 20 THEN 1 ELSE 0 END CASE) AS timer_up_to_20,
       SUM(CASE WHEN timer > 20 AND timer <= 60 THEN 1 ELSE 0 END CASE) AS timer_20_to_60,
       SUM(CASE WHEN timer > 60 AND timer <= 120 THEN 1 ELSE 0 END CASE) AS timer_60_to_120,
       SUM(CASE WHEN timer > 120 THEN 1 ELSE 0 END CASE) AS timer_over_120
FROM   my_table
GROUP BY user_name

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[DBrewsky]

That worked beautifully! However, I did have to change END CASE to just END.

Thank you very much!!

--DB