CODEWITHSUNDEEP
mysqlsqlpivot
AnilHoney
AnilHoney

Reputation: 249

How to get count in single row?

I am just trying to get knowledge on mysql 

 mysql> SELECT species, sex, COUNT(*) FROM pet
        -> WHERE species = 'dog' OR species = 'cat'
        -> GROUP BY species, sex;

+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

Is it possible to get? 

+---------+-----------+-------------+
| species | malecount | femalecount |
+---------+-----------+-------------+
| cat     | 1         |       1     |
| dog     | 1         |       2     |
+---------+-----------+-------------+

In single query?

Upvotes: 2

Views: 99

Answers (2)

John Woo
John Woo

Reputation: 263733

This is the shortest possible answer in MySQL.

SELECT  species,
        SUM(sex = 'm') maleCount,
        SUM(sex = 'f') femaleCount
FROM    pet
GROUP   BY species

MySQL also supports inline IF statement:

SELECT  species,
        SUM(IF(sex = 'm',1,0)) maleCount,
        SUM(IF(sex = 'f',1,0)) femaleCount
FROM    pet
GROUP   BY species

You can also use CASE which works on almost RDBMS:

SELECT  species,
        SUM(CASE WHEN sex = 'm' THEN 1 ELSE 0 END) maleCount,
        SUM(CASE WHEN sex = 'f' THEN 1 ELSE 0 END) femaleCount
FROM    pet
GROUP   BY species

Upvotes: 8

Taryn
Taryn

Reputation: 247720

You can use an aggregate function with a CASE expression to pivot the data from rows into columns:

select species,
  sum(case when sex = 'm' then 1 else 0 end) maleCount,
  sum(case when sex = 'f' then 1 else 0 end) femaleCount
from pet
group by species

Upvotes: 6

Related Questions