MySQL Combining selected columns of multiple rows into one

Question

I want to count the number of male & female user of each religion of each age to obtain a resulting table like the third table below.

I have two tables as below:

tbl_user

+----+----------+------------+--------+-----------+
| id |   name   |    dob     | gender | religion  |
+----+----------+------------+--------+-----------+
|  1 | raj      | 1999-12-21 | m      |         1 |
|  7 | raju     | 1998-10-10 | m      |         2 |
|  8 | rajan    | 2000-11-23 | m      |         3 |
| 11 | neetu    | 1992-12-06 | f      |         1 |
| 12 | sita     | 1993-06-16 | f      |         2 |
| 13 | rita     | 1992-06-08 | f      |         3 |
| 14 | jenny    | 1993-05-10 | f      |         2 |
| 15 | manju    | 1993-12-16 | f      |         1 |
| 16 | aanju    | 1993-03-05 | f      |         3 |
| 17 | raja     | 1995-04-06 | m      |         1 |
| 18 | rajendra | 1995-07-03 | m      |         2 |
| 19 | rajesh   | 1991-05-02 | m      |         3 |
+----+----------+------------+--------+-----------+

tbl_religion

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Christian |
|  2 | Hindu     |
|  3 | Islam     |
+----+-----------+

The religion table can have any number of records(religions).

Now I want to count the number of male & female user of each religion of each age to obtain a resulting table like the one below. The user can be of any age or or born on any year:

+-----+----------------+------------------+------------+--------------+------------+--------------+
| Age | Christian Male | Christian Female | Hindu Male | Hindu Female | Islam Male | Islam Female |
+-----+----------------+------------------+------------+--------------+------------+--------------+
|  14 |              0 |                0 |          0 |            0 |          1 |            0 |
|  15 |              1 |                0 |          0 |            0 |          0 |            0 |
|  16 |              0 |                0 |          1 |            0 |          0 |            0 |
|  20 |              1 |                0 |          1 |            0 |          0 |            0 |
|  21 |              0 |                1 |          0 |            0 |          0 |            0 |
|  22 |              0 |                1 |          0 |            2 |          0 |            1 |
|  23 |              0 |                0 |          0 |            0 |          1 |            1 |
|  24 |              0 |                0 |          0 |            0 |          0 |            0 |
+-----+----------------+------------------+------------+--------------+------------+--------------+

Thank you for any help.

@jpw: Yep. but I missed somtething in the question yesterday that the religion table can have any number of entries. I tried myself to do so on the code u provided. But can't get the required result. So, posted this question again with the correction. — Rajan Acharya, Sep 09 '15 at 10:41
@e4c5: the relation between tbl_religion & tbl_user is that tbl_user contains the id of the religion as foreign key to get the name of the religion — Rajan Acharya, Sep 09 '15 at 10:42
@RajanAcharya Oh ok, I didn't notice the added information. To do what you want you'll need to use dynamic sql with a prepared statement. I'll post an answer soon. — jpw, Sep 09 '15 at 10:44

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

Building on my answer to your previous question, we can use the method described in this q/a to make the query dynamic so that it handles an unknown number of religions.

The final query will look like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(gender = ''', gender,
      ''' and r.name = ''',
      r.name, ''') AS "',
      if(gender='m','Male','Female'), 
      ' ',  r.name, '" '
    )
  ) INTO @sql
FROM
  tbl_user u
JOIN 
  tbl_religion r ON u.religion = r.id;

SET @sql = CONCAT(
  'select timestampdiff(year, dob, now()) age, ', @sql, ' 
  from tbl_user u
  join tbl_religion r on u.religion = r.id
  group by timestampdiff(year, dob, now());');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Sample SQL Fiddle

edited May 23 '17 at 10:26

Community

1
1

answered Sep 09 '15 at 11:07

jpw

44,361
6
66
86

Thanks @jpw. Really appreciate ur effort. Now I will try to get that prepared statement to work in my php. – Rajan Acharya Sep 09 '15 at 13:08
I tried to run your prepared statement on my php but can't do it. – Rajan Acharya Sep 10 '15 at 08:08
@RajanAcharya What happens when you try it? – jpw Sep 10 '15 at 17:16

Stephan B · Answer 2 · 2015-09-09T11:37:16.230

You could select the columns by hand:

SELECT
  TIMESTAMPDIFF(YEAR, u.dob, CURRENT_DATE) AS 'Age',
  SUM(CASE
        WHEN r.name = 'Christian' AND u.gender = 'm' THEN 1
        ELSE 0
      END) AS 'Christian Male',
  SUM(CASE
        WHEN r.name = 'Christian' AND u.gender = 'f' THEN 1
        ELSE 0
      END) AS 'Christian Female'
FROM tbl_user u
LEFT JOIN tbl_religion r ON r.id = u.religion

But then you would need to change your query for each new religion. I suppose you should generate an intermediate query and use a Pivot Function to transpose the Description into multiple columns later:

SELECT
  TIMESTAMPDIFF(YEAR, u.dob, CURRENT_DATE) AS 'Age',
  CONCAT(r.name, ' ', CASE
    WHEN u.gender = 'm' THEN 'Male'
    WHEN u.gender = 'f' THEN 'Female'
    ELSE 'Unknown'
  END) AS 'Description'
FROM tbl_user u
LEFT JOIN tbl_religion r ON r.id = u.religion

Wich gives you the following output:

+-----+------------------+
| Age | Description      |
+-----+------------------+
| 14  | Christian Male   |
| 15  | Christian Female |
| 16  | Hindu Male       |
| 20  | Hindu Female     |
| 21  | Islam Male       |
+-----+------------------+

Then you turn it into your desired output using Pivot, using Excel or some other tool. I do not know if this can be done in MySQL directly but there are multiple SO Questsions about this.

score -1 · Answer 3 · answered Sep 09 '15 at 10:35

-1

For the first two tables

select count(a.id) as 'Population',
       timestampdiff(year,a.dob,current_date)as 'Age',
       b.name as 'Religion'
from   tbl_user a
inner join tbl_religion b on a.ethnicity=b.name
group by
timestampdiff(year,a.dob,current_date),
       b.name

for the third table

select sum(Christian_Male)+sum(Christian_Female)as 'Christian',
       sum(Hindu_Male)+sum(Hindu_Female)as 'Hindu',
       sum(Islam_Male)+sum(Islam_Female)as 'Islam',
       Age
from religion_table
group by
       Age

answered Sep 09 '15 at 10:35

user3600910

2,839
4
22
36

I think the `tbl_religion` is to be joined on `a.ethnicity = b.id` in your first query. – Stephan B Sep 09 '15 at 10:52
i thought you wanted two different queries, i will add another answer with one query – user3600910 Sep 09 '15 at 11:40

MySQL Combining selected columns of multiple rows into one

3 Answers3