MySQL Grouping by

Question

I have one table in MySQL:

tblUser 
    - name      varchar(200)
    - sex       varchar(200)
    - signDate` datetime

I am looking for the following result:

monthDate   M    F
01          0    1
06          0    2
07          0    2
08          0    2
09          0    2
10          1    0
11          1    0
12          0    2

But I am getting the following, my problem is I am not able to figure out how could I show the result for Sex M and F in one line as the previous result that I am looking for.

My result:

monthDate   sex   count(1)
 1          F     1
 6          F     2
 7          F     2
 8          F     2
 9          F     2
10          M     1
11          M     1
12          F     2

My query:

SELECT monthDate
      ,sex
      ,count(1)
FROM
(
    SELECT name
        ,sex
        ,MONTH(signDate) monthDate
    FROM  dbPaciente.tblUser
    GROUP BY monthDate
            ,sex
    ORDER BY monthDate asc
            ,sex
) tableResult
GROUP BY monthDate;

Could you please give me an idea about how to solve it, please? Thanks in advance,

https://stackoverflow.com/questions/7674786/mysql-pivot-table — Paul Spiegel, May 25 '18 at 20:43

score 1 · Accepted Answer · answered May 25 '18 at 20:29

1

SELECT monthDate
      ,count(case when sex='f' then sex else null end)F
      ,count(case when sex='m' then sex else null end)M
FROM
(
    SELECT name
        ,sex
        ,MONTH(signDate) monthDate
    FROM  dbPaciente.tblUser
    GROUP BY monthDate
            ,sex
    ORDER BY monthDate asc
            ,sex
) tableResult
GROUP BY monthDate

answered May 25 '18 at 20:29

Daniel Marcus

2,686
1
7
13

Use conditional aggregation – Daniel Marcus May 25 '18 at 20:29
Dear Daniel, that is amazing! Thank you very much, just I put wrong "GROUP BY monthDate ,sex" in the inside query, which it has not to be there, is perfect now, thanks again :) – felipe May 25 '18 at 20:44
You're welcome anytime – Daniel Marcus May 25 '18 at 20:46

MySQL Grouping by

1 Answers1