CODEWITHSUNDEEP
phpmysql
Seph1603
Seph1603

Reputation: 99

Rows Into Columns Queries MySql

I have this table:

ID   CODE   WEEK  SEX
1    abc    1     F
2    abc    2     M
3    xyz    3     F
4    abc    1     M

and I am using this query to filter the data:

SELECT `code`,`week`,`sex`, COUNT(`week`) as cnt 
FROM `table` 
WHERE `code` = "abc" 
and `sex` = "F" 
group by `week` 
having (`week` > 0) 
UNION ALL 
SELECT `code`,`week`,`sex`, COUNT(`week`) as cnt 
FROM `table` 
WHERE `code` = "abc" 
and `sex` = "M"
group by `week` 
having (`week` > 0)

and this is the result: 

CODE   WEEK   SEX  cnt
abc    1      F    1
abc    1      M    1
abc    2      M    1

But now I need to show the data in this way:

CODE   WEEK  M  F
abc    1     1  1
abc    2     1  0

So I have this query:

SELECT  
`WEEK`,`CODE`,  
GROUP_CONCAT(if(`SEX` = "F", `cnt`, NULL)) AS "F", 
GROUP_CONCAT(if(`SEX` = "M", `cnt`, NULL)) AS "M" 
FROM `temp_table`
GROUP BY `WEEK` 
ORDER BY CAST(`WEEK` AS UNSIGNED)

How can I combine this 2 queries? Is there a better way to do this?

Upvotes: 1

Views: 31

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

SELECT `WEEK`,`CODE`
, SUM(IF(`SEX` = "F", cnt, 0)) AS `F`
, SUM(IF(`SEX` = "M", cnt, 0)) AS `M`
FROM `table`
GROUP BY `WEEK`,`CODE`
;

I'm not sure why you were doing that strange ORDER BY, and I was pretty sure you would want to group on CODE as well. (Edit: Yes, the CAST was appropriate for the data type of week.)

If using the first query as the "source", see below:

SELECT `WEEK`,`CODE`
, SUM(IF(`SEX` = "F", 1, 0)) AS `F`
, SUM(IF(`SEX` = "M", 1, 0)) AS `M`
FROM ([original query goes in here]) `subQ`
GROUP BY `WEEK`,`CODE`
;

Upvotes: 2

Related Questions