mysql reporting, analytics query

Question

I want to create mysql query to count me some data, without where clause, not like:

select count(Gender),concat(Gender), Year from sometable where Year = 2015 group by Gender
union
select count(Gender),concat(Gender), Year from sometable where Year = 2016 group by Gender;

but more like:

select Gender,Year something to count ....

here is example:

in table i have

---------------
Gender | Year
---------------
Male     2016
Male     2015
Female   2015
Female   2016
---------------

end result to be

          ---------------
           2015 | 2016
          ---------------
Male        1       1
Female      1       1
          ---------------

Would `select gender, year, count(*) from table group by gender, year` suffice? — Antti29, Nov 04 '16 at 07:11
Seems like the post is similar to http://stackoverflow.com/questions/1241178/mysql-rows-to-columns.Creating columns dynamically from row values is a problem.I have given one temporary solution.Check if it would suffice. — Kiran Muralee, Nov 04 '16 at 07:35

score 1 · Answer 1 · answered Nov 04 '16 at 07:22

1

Let us assume the table name as gender_count.To get the result like you wanted try the following query

SELECT DISTINCT
    gender,
    (SELECT 
            COUNT(*)
        FROM
            gender_count
        WHERE
            year = '2015' AND gender = gc.gender) '2015',
    (SELECT 
            COUNT(*)
        FROM
            gender_count
        WHERE
            year = '2016' AND gender = gc.gender) '2016'
FROM
    gender_count gc

answered Nov 04 '16 at 07:22

Kiran Muralee

2,068
2
18
25

What about years before 2015 and after 2016? – Antti29 Nov 04 '16 at 07:24
Yeah I know that would not be possible,the OP asked for it,that is why given this solution.Let us wait for the OP's opinion.Constructing columns dynamically I think would be some problem. – Kiran Muralee Nov 04 '16 at 07:27

mysql reporting, analytics query

1 Answers1