Skip to content
Advertisement

How to select from chldren with parent table as one row

I’m struggling to fetch all results from member_details table as one row and group them by memberId. Here is my tables structure:

//members

    +---------------------------+
    | memberId  | stateId       |
    +-----------+---------------+
    |  1        |  101          |
    |  2        |  103          |
    |  3        |  109          |
    |  4        |  109          |
    +---------------------------+

//member_details

    +--------------------------------------------------+
    |id |memberId |gender       |age_type       |number|
    +---+---------+-------------+---------------+------+
    |1  |4        |male         |childs         |2000  |
    |2  |4        |male         |adults         |590   |
    |3  |4        |male         |elders         |900   |
    |4  |4        |female       |childs         |2000  |
    |4  |4        |female       |adults         |2000  |
    |4  |4        |female       |elders         |2000  |
    +--------------------------------------------------+

//result table view

    +--------------------------------------------------------------------------+
    | childs | childs  | adults | adults | elders | elders | total  | total    |
    | (male) |(female) | (male) |(female)|(male)  |(female)| (male) | (female) |
    +--------+---------+--------+--------+--------+--------+-------------------+
    | 2000   | 2000    | 590    | 2000   | 900    |2000    | 5590   | 6000     |
    +--------------------------------------------------------------------------+

//sql

    SELECT *.m, *.md 
    FROM members m
    JOIN member_details md 
    ON m.memberId = md.memberId
    where m.stateId = 109
    GROUP BY md.memberId

Advertisement

Answer

you can use case expression as following

select
  sum(case when gender = 'male' and age_type = 'childs' then number else 0 end) as total_male_childs,
  sum(case when gender = 'male' and age_type = 'adults' then number else 0 end) as total_male_adults,
  sum(case when gender = 'male' and age_type = 'elders' then number else 0 end) as total_male_elders,
  sum(case when gender = 'female' and age_type = 'childs' then number else 0 end) as total_female_childs,
  sum(case when gender = 'female' and age_type = 'adults' then number else 0 end) as total_female_adults,
  sum(case when gender = 'female' and age_type = 'elders' then number else 0 end) as total_female_elders    
from member_dtails
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement