CODEWITHSUNDEEP
mysqlsql
Brian Coolidge
Brian Coolidge

Reputation: 4649

Mysql select with CASE not retrieving other column value?

As the answer that I got receive from my question which drive me nuts applying to my code for some reason, why it did not worked.

Here's a part of my data on table.

enter image description here

Original Code:

SELECT AGE_RANGE, COUNT(*) FROM (
    SELECT CASE
        WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
        WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
        WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
        WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
        WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
        WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
        WHEN YearsOld > 40 THEN '40+'
        END AS 'AGE_RANGE'
        FROM (
            SELECT YEAR(CURDATE())-YEAR(DATE(birthdate)) 'YearsOld' 
            FROM MyTable
        ) B
) A
GROUP BY AGE_RANGE

and here is the result.

enter image description here

What I'm trying to do is, I'm trying to add another column which would count on how many people who is in that area which would be location as you would see on the picture on top which includes Perth, Western Australia, Sunbury, Victoria and so on.

First attempt to fix my problem

As you would see below, I've added location, and COUNT(location) loc to get the name of the location and count on how many location that is duplicated in the table.

SELECT AGE_RANGE, COUNT(*), location, COUNT(location) loc FROM (
    SELECT CASE
        WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
        WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
        WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
        WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
        WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
        WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
        WHEN YearsOld > 40 THEN '40+'
        END AS 'AGE_RANGE', 'location'
        FROM (
            SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld' 
            FROM event_participants
        ) B
) A
FROM event_participants WHERE location <> '' GROUP BY location HAVING loc >= 1 ORDER BY loc DESC LIMIT 5

Which results to..

enter image description here

Obviously, hearing from the aid of god doesn't work out to well..

My second attempt to fix this problem

SELECT AGE_RANGE, COUNT(*), location FROM (
    SELECT CASE
        WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
        WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
        WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
        WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
        WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
        WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
        WHEN YearsOld > 40 THEN '40+'
        END AS 'AGE_RANGE', 'location'
        FROM (
            SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld' 
            FROM event_participants
        ) B
) A
GROUP BY AGE_RANGE

And the result is...

enter image description here

Instead of showing the name of the countries, it only show location.

Well..

Long story short, what's wrong with the code? Any solution for this stuff?

It was like, my desired result would be,

AGE_RANGE COUNT(*) location            total number of same location

16-20        4     Name of location                 #

21-30       45     Name of location                 #

31-40       79     Name of location                 #

40+        102     Name of location                 #

Upvotes: 0

Views: 433

Answers (3)

Matt MacLean
Matt MacLean

Reputation: 19648

This should work: Think of each subquery as it's own table, so you just need to make sure to select all the columns you need. (CASE WHEN THEN END AS) is actually just a single column value. You will probably also want to group on location so you can get the same age ranges for different locations. (Ex. 11-15 in Canada and 11-15 in USA)

SELECT AGE_RANGE, COUNT(*), A.location FROM (
        SELECT CASE
            WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
            WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
            WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
            WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
            WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
            WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
            WHEN YearsOld > 40 THEN '40+'
            END AS 'AGE_RANGE', B.location
            FROM (
                SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld',
                location /* << just missing this select */
                FROM event_participants
            ) B
    ) A
    GROUP BY A.location, AGE_RANGE

Upvotes: 1

Nisarg
Nisarg

Reputation: 3262

Try this 

SELECT AGE_RANGE, COUNT(*), location FROM (
        SELECT CASE
            WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
            WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
            WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
            WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
            WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
            WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
            WHEN YearsOld > 40 THEN '40+'
            END AS 'AGE_RANGE', 'location'
            FROM (
                SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld'
                FROM event_participants
            ) B
    ) A
    GROUP BY location, AGE_RANGE

Upvotes: 1

Vivek Sadh
Vivek Sadh

Reputation: 4268

This is not correct:-

SELECT AGE_RANGE, COUNT(*), location FROM

When you use GROUP BY the attribute list in select must be applied either to aggregate function or must be a part of GROUP BY clause.

In the example below you cannot show the name of the location as the person in the age-group may belong to different location:-

AGE_RANGE COUNT(*) location            total number of same location

16-20        4     Name of location                 #

21-30       45     Name of location                 #

Upvotes: 0

Related Questions