SQL to Populate an Array with a Group by...

[Javamahn]

I have a table that has 5 different rating columns (varchar). Each column will contain values excellent, good, adequate, or poor. I would like to tally the count of each value for each column and put that into an array for graphing. The results would be some like this.

Speaker: Excellent = 5
Good = 3
Adequate = 2
Poor = 0
Food: Excellent = 3
Good = 2

Etc.

Can this be done with one SQL or do I need multiple Statements using group by to get this done?

Thank you in advance.

 

[TonyGroves]

Do you mean your table has five columns, named "excellent" to "poor", and the "excellent" column contains the string value "excellent" or null, the "poor" column contains "poor" or null, etc?

If so, then you could use:
[tt]
SELECT
idfield,
count(excellent) excellent,
count(good) good,
count(adequate) adequate,
count(poor) poor
FROM tblname
GROUP BY idfield
[/tt]
However, that doesn't seem like great table design. Could you not just use one rating column, with a numeric value?

 

[TonyGroves]

Sorry, I misread your question. You can forget my previous reply. I'll have another look at it.

 

[TonyGroves]

You could use something like:
[tt]
SELECT
'speaker' aspect,speaker rating,COUNT(*) tally
FROM tblname
GROUP BY speaker
UNION ALL
SELECT
'food',food rating,COUNT(*)
FROM tblname
GROUP BY food
UNION ALL
...
[/tt]

 

[Javamahn]

Thank you