Rows Into Columns Queries MySql

Question

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?

It's not related but for that first query you could've avoided the UNION by just grouping on week and sex. — Uueerdo
– Uueerdo, Commented May 12, 2015 at 19:04

Uueerdo · Accepted Answer · 2015-05-12 19:53:21Z

2

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`
;

edited May 12, 2015 at 19:53

answered May 12, 2015 at 19:06

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Seph1603 Over a year ago

It is because on the real table the "Week" column is VARCHAR, so when I am grouping by `WEEK it shows 10,2,32,3 ... etc

Uueerdo Over a year ago

Ah, that would explain it.

Seph1603 Over a year ago

Instead of having SUM(IF(SEX = "F", 1, 0)) AS F , how can I add the cnt column in the firts query? like: SUM(IF(SEX = "F", cnt, 0)) AS F

Uueerdo Over a year ago

The cnt column does not exist in this example as it is intended to operate off the original table, not the results of the first query; but if it were using those results instead, you could just substitute cnt for the 1s.

Seph1603 Over a year ago

yes, I need to use the result of the first query with your query. Thats why I need to combine them but I don´t know how.

Collectives™ on Stack Overflow

Rows Into Columns Queries MySql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related