maikelsabido - 1 year ago 43
SQL Question

# Group data by age, gender and report requested in MySQL

I'm having trouble formulating my MySQL statement / query string. I have a table (see sample below)

It's a sample table containing gender, age and report_requested fields. What I would like to have as an output is something like this:

``````20 years old, male | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports

20 years old female | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports

20 years old combined | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports

21 years old, male | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports

21 years old female | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports

21 years old combined | Total number of users | Total who requested min 1 report | total who requested 2 reports | total 3 or more reports
``````

... etc.

But I'm having a hard time with it. What I just know is to determine the number of users who requested (1,2,3,...etc) credit reports given the gender and age.

Here's what I used:

``````SELECT COUNT(*) as cnt, report_requested
FROM sampletable WHERE age = '39'
AND gender = 'M' GROUP BY report_requested
``````

Here's the result:

It just return the data for 20 yrs old male, number of users requested 1 credit report, 2 credit reports up to 8 (but this is also wrong since it should combine the number of users who requested 3 credit reports or more)

Can anybody here help me or give me an idea on how I could accomplish this?

Your `GROUP BY` clause is actually both `age` and `gender` since you are trying to aggregate for these two. The way to think about it is that you want exactly one row per `age` and `gender`, i.e. 1 row for male/20 yrs, 1 row for female/20 yrs, 1 row for male/21 yrs, etc. So you would do:

``````GROUP BY age, gender
``````

And instead of `report_requested` column, I think you need to `SUM(report_requested)` with a condition on the number of reports requested. This is handled in SQL via the `CASE` clause. So your query would look like this:

``````SELECT AGE, GENDER,
SUM(CASE WHEN report_requested = 1 THEN 1 ELSE 0 END) AS 'Total who requested 1 report',
SUM(CASE WHEN report_requested = 2 THEN 1 ELSE 0 END) AS 'Total who requested 2 reports',
SUM(CASE WHEN report_requested >= 3 THEN 1 ELSE 0 END) AS 'Total who requested 3 or more reports'
FROM sampletable
GROUP BY AGE, GENDER
``````

Let me know how it goes. I removed the `WHERE` clause because I assumed that was for testing only.

EDIT: Updated after the comments below, that it's not the total requested rather total who requested 1 report, total who requested 2 reports, etc.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download