MySQL Crosstab query?

[Tbull]

Using PHP 4.0.4pl1 and MySQL 3.23.33. An MySQL table has the following columns of interest:

UID autoincrement
HuName char human readable
EREC char
AT_date date field
... other columns

The text in the column EREC can have a wide range of entries, but the most common are:
RED, AMBER, GREEN, OFF and FAILED. It seems that a query should be able to construct a crosstab type output such that the output on a series grouped by say HuName could show the totals something like (spacing is off in this post):

Name RED Amber Green Off Fail Other Total
HuName1 2 2 5 1 1 0 11
HuName2 3 1 7 2 2 1 16
... n+1

Can someone see how this should be constructed using a query. I keep thinking that it should be able to be done using a Case in the query, but keep failing. A query would appear to provide all of the benefits of speed and compactness too.

Thanks for any suggestions.

[nnichols]

Give this a try -

SELECT HuName, 
       SUM(IF(EREC=RED,1,0)) AS RED,
       SUM(IF(EREC=AMBER,1,0)) AS AMBER,
       SUM(IF(EREC=GREEN,1,0)) AS GREEN,
       SUM(IF(EREC=OFF,1,0)) AS OFF,
       SUM(IF(EREC=FAIL,1,0)) AS FAIL
FROM tbl_name
GROUP BY HuName, EREC

I haven't done the OTHER and TOTAL columns because my brain is fried at this precise moment.

If it is producing what you want, so far, then post again with a small datadump so I can test the query as I go.

Hope this helps 😉

[Tbull]

Thank you nnichols!

This works [if I don't group on Erec - which will generate a seperate row for each IF(...)]. Now I have some homework to do to get all the possiblities into an array, pass them to a query and total each row, etc.

Thank you so very much for getting the heavy lifting on track. My brain was getting full trying to get this to work.

Tbull