CODEWITHSUNDEEP
mysqlselectgroup-bycase
ToddN
ToddN

Reputation: 2961

MySql Multiple Column SELECT from Single Column Table with Grouping

I want to display data like such where I am GROUP on DATE:

Date        Star1   Star2   Star3   Star4   Star5
2012-10-25  null    null    null     1       1

My Current table has three (3) columns, displays:

ID DATE        RATING
5  2012-10-25    4
6  2012-10-25    5

I am using this query to try to achieve the above data:

SELECT 
    DATE,
    CASE
        WHEN RATING = 1 THEN 1
        ELSE NULL
    END AS STAR1,
    CASE
        WHEN RATING = 2 THEN 1
        ELSE NULL
    END AS STAR2,
    CASE
        WHEN RATING = 3 THEN 1
        ELSE NULL
    END AS STAR3,
    CASE
        WHEN RATING = 4 THEN 1
        ELSE NULL
    END AS STAR4,
    CASE
        WHEN RATING = 5 THEN 1
        ELSE NULL
    END AS STAR5
FROM
    table1
WHERE
    DATE = '2012-10-25'
GROUP BY DATE
ORDER BY DATE

This only spits out one rating tho:

Date        Star1   Star2   Star3   Star4   Star5
2012-10-25  null    null    null    null      1

Its like it is not GROUPing on Date, how can I achieve this?

Upvotes: 1

Views: 259

Answers (2)

Gutenberg
Gutenberg

Reputation: 1002

SELECT 
DISTINCT DATE, 
(SELECT COUNT(RATING) FROM Table1 WHERE RATING = '1') AS Star1,
(SELECT COUNT(RATING) FROM Table1 WHERE RATING = '2') AS Star2,
(SELECT COUNT(RATING) FROM Table1 WHERE RATING = '3') AS Star3,
(SELECT COUNT(RATING) FROM Table1 WHERE RATING = '4') AS Star4,
(SELECT COUNT(RATING) FROM Table1 WHERE RATING = '5') AS Star5
FROM 
Table1
WHERE 
DATE = '2012-10-25'
GROUP BY DATE
ORDER BY DATE

Upvotes: 1

MikeB
MikeB

Reputation: 1533

It's a bit surprising that MySQL allows such a query, since the GROUP BY doesn't cover all the non-aggregated columns in your SELECT list.

If you wrap your CASE statements into counts, then I think you'll end up with what you want:

SELECT 
    DATE,
    COUNT(CASE
        WHEN RATING = 1 THEN 1
        ELSE NULL
    END) AS STAR1,
    COUNT(CASE
        WHEN RATING = 2 THEN 1
        ELSE NULL
    END) AS STAR2,
    COUNT(CASE
        WHEN RATING = 3 THEN 1
        ELSE NULL
    END) AS STAR3,
    COUNT(CASE
        WHEN RATING = 4 THEN 1
        ELSE NULL
    END) AS STAR4,
    COUNT(CASE
        WHEN RATING = 5 THEN 1
        ELSE NULL
    END) AS STAR5
FROM
    table1
WHERE
    DATE = '2012-10-25'
GROUP BY DATE
ORDER BY DATE

Please let me know how you do with that approach.

Upvotes: 3

Related Questions