CODEWITHSUNDEEP
mysql
Dhirender
Dhirender

Reputation: 634

Group data by single column but want to show them as column

I am working on my small project in which I have a situation in which, we need to show report like below: 

gender    gender_count    rejected     selected  
Male      230             50           180  
Female    150             20           130

All values in above report(excluding gender column) are count from table.

Table structure is given below: 

 user_id     gender    status  
 1           male      selected  
 2           male      selected  
 3           female    rejected  
 4           female    selected  
 5           male      rejected

I am not able to write query for this kind of report. I was using group by to count gender. but how can I count gender by status and then make them as column.

Please help me on this. Thanks in advance.

Upvotes: 0

Views: 35

Answers (4)

Arunkumar Muthuvel
Arunkumar Muthuvel

Reputation: 433

for your case 

 mysql> select   * from genderlist;
    +---------+--------+--------+
    | user_id | gender | status |
    +---------+--------+--------+
    |       1 | male   | select |
    |       2 | female | select |
    |       3 | female | reject |
    |       4 | female | reject |
    |       5 | male   | reject |
    |       6 | male   | select |
    +---------+--------+--------+

try the below query

SELECT gender,count(*) as gendercount,sum(status='select') as 'selected',sum(status='reject') as reject from genderlist group by gender;

output will be

mysql> SELECT gender,count(*) as gendercount,sum(status='select') as 'selected',sum(status='reject') as reject from genderlist group by gender;
+--------+-------------+----------+--------+
| gender | gendercount | selected | reject |
+--------+-------------+----------+--------+
| female |           3 |        1 |      2 |
| male   |           3 |        2 |      1 |
+--------+-------------+----------+--------+

Upvotes: 0

Hari17
Hari17

Reputation: 489

I've checked through SQLfiddle and the link is provided below you can check it... http://sqlfiddle.com/#!9/822019/3

 CREATE TABLE table1 (
        user_id int,
        gender varchar(255),
        status varchar(255)

    );

    INSERT INTO table1 VALUES (1,"male","selected"),
    (2,"male","selected"),
    (3,"female","rejected"),
    (4,"female","selected"),
    (5,"male","rejected");

SELECT
gender, COUNT(*) GENDER_COUNT, 
SUM(IF(STATUS='REJECTED',1,0)) REJECTED,
SUM(IF(STATUS='SELECTED',1,0)) SELECTED
FROM 
table1
GROUP BY gender

Upvotes: 0

cdaiga
cdaiga

Reputation: 4939

Try this:

SELECT gender, COUNT(*) gender_count, SUM(status='rejected') rejected, 
       SUM(status='selected') selected 
FROM yourTable
GROUP BY gender;

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

SELECT
GENDER, COUNT(*) GENDER_COUNT, 
SUM(IF(STATUS='REJECTED',1,0)) REJECTED,
SUM(IF(STATUS='SELECTED',1,0)) SELECTED
FROM 
TABLE
GROUP BY GENDER

Other DBs you would need to use CASE instead of IF

(I am not a MySQL expert but this should point you in the right way)

Upvotes: 1

Related Questions