CODEWITHSUNDEEP
phpmysql
bla
bla

Reputation: 31

mysql reporting, analytics query

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

Upvotes: 3

Views: 122

Answers (1)

Kiran Muralee
Kiran Muralee

Reputation: 2060

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

Upvotes: 1

Related Questions