sum of count of column for group by two columns mysql

Question

INSERT INTO tablex(Id, Name, Team, Joined) VALUES

(1, 'Mr. A', 'X', '2011-02-22'),

(2, 'Mr. B', 'Y', '2011-02-11'),

(3, 'Ms. C', 'X', '2011-02-10'),

(4, 'Mr. D', 'Y', '2011-01-12'),

(5, 'Ms. E', 'X', '2011-01-06'),

(6, 'Mr. F', 'Y', '2011-05-02');

(7, 'Mr. H', 'X', '2011-02-01');

output required is:

Month   | Team | TotalMembers | Percentage

 01/2011  | X    |   1          |   50

 01/2011  | Y    |   1          |   50

 02/2011  | X    |   2          |   75

 02/2011  | Y    |   1          |   25

 05/2011  | X    |   0          |   0

 05/2011  | Y    |   1          |   100

Please help me to do the above. Mostly preferred in MySQL (GROUP BY Team, DATE_FORMAT(Joined, '%m/%Y')). but can use PHP.

Thanks in advance

Ca you explain the Percentage column? If it is, as I suppose it is, the ratio of members joined in one team to all members joined that month then rows 3 and 4 are not correct, should stay 66.6 and 33.3 ? — Relja
– Relja, Commented Oct 12, 2011 at 11:16
Is it necessary to have a 0 record for 5/11, team X? Won't that cause a lot of 0 records to appear in the output if you have another team, say team Z? — mellamokb
– mellamokb, Commented Oct 12, 2011 at 14:53

Nin · Accepted Answer · 2011-10-12 11:45:49Z

2

There can be more elegant solutions but this one should work:

SELECT DATE_FORMAT( Joined, '%m/%Y' ) AS
    MONTH , team, (
    count( id ) / (
    SELECT count( * )
    FROM tablex
    WHERE DATE_FORMAT( Joined, '%m/%Y' ) = DATE_FORMAT( tx.Joined, '%m/%Y' )
    GROUP BY DATE_FORMAT( Joined, '%m/%Y' ) ) *100
    ) AS percentage
    FROM `tablex` AS tx
    GROUP BY DATE_FORMAT( Joined, '%m/%Y' ) , team

answered Oct 12, 2011 at 11:45

Nin

3,0201 gold badge24 silver badges31 bronze badges

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

4 Comments

mellamokb Over a year ago

+1 Excellent answer! One minor issue though -- it doesn't include the 5/11 record for X that has 0 team members: sqlize.com/164lD5bwbw

Nin Over a year ago

But what if you have a team Z on 06/11, will you get a lot of records with 0's for X and Y? If you want that, you're looking at a whole different solution.First retreive all possible teams, then query the db for all the months. Then for each month calculate the total members and percentage and add 0 for non-existing teams. You could probably do it in one query but performance will be a disaster

mellamokb Over a year ago

I agree with you. I'm just noting the discrepency between your solution and what the OP posted as the "required output". I would take that concern up with the OP.

Sailesh Jaiswal Over a year ago

Thanks Nin! It was very useful. In continuation to this I've posted other query please have a look at this link. link

Collectives™ on Stack Overflow

sum of count of column for group by two columns mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related