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