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
– Antti29, Commented Nov 4, 2016 at 7:11
Seems like the post is similar to stackoverflow.com/questions/1241178/… columns dynamically from row values is a problem.I have given one temporary solution.Check if it would suffice. — Kiran Muralee
– Kiran Muralee, Commented Nov 4, 2016 at 7:35

Kiran Muralee · Accepted Answer · 2016-11-04 07:22:31Z

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 4, 2016 at 7:22

Kiran Muralee

2,0802 gold badges19 silver badges28 bronze badges

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

2 Comments

Antti29 Over a year ago

What about years before 2015 and after 2016?

Kiran Muralee Over a year ago

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.

Collectives™ on Stack Overflow

mysql reporting, analytics query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related