MYSQL count in select statement

Question

I have data in database like this

id  class  gender
1     A      F
2     B      F
3     A      M
4     A      F
5     A      M
6     B      M
7     A      F

From this data I want to make select statement to produce report like this

_________________________
        Gender
class   M    F     Total
_________________________
A       2    3       5
B       1    1       2
_________________________
TOTAL   3    4       7

How can I make that select statement ?

the primary purpose of having a database server is to store the data, not for generating report, the counts are possible but by formatting makes it difficult, why not do it on your application level? — John Woo
– John Woo, Commented Oct 10, 2012 at 4:22
Thanks, now I get some idea. I will count the total in the application. But how can I get total gender based on class ? — anon
– anon, Commented Oct 10, 2012 at 4:32

Adriaan Stander · Accepted Answer · 2012-10-10 04:27:43Z

5

Have a look at the following example

SQL Fiddle DEMO

SELECT class,
      SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) `M`,
      SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) `F`,
      COUNT(1) Total
FROM Table1
GROUP BY class

answered Oct 10, 2012 at 4:27

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

Geoff Montee · Accepted Answer · 2012-10-10 04:28:45Z

2

To get totals for each gender:

SELECT class, gender, COUNT(*) as gender_count
FROM Gender
GROUP BY class, gender;

To get total:

SELECT class, COUNT(*) as total_count
FROM Gender
GROUP BY class;

answered Oct 10, 2012 at 4:28

Geoff Montee

2,59715 silver badges14 bronze badges

Collectives™ on Stack Overflow

MYSQL count in select statement

2 Answers 2

SQL Fiddle DEMO

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related