CODEWITHSUNDEEP
mysqldatabase
Jaren
Jaren

Reputation: 63

MySQL - grouping data by columns

As a follow up to my first post where I was asking for help manipulating data into an excel pivot table, I'm of the presumption that this can only be done via a database command using unions. Thus I'm submitting a new question with the same data, but from a MySQL standpoint.

I have the follwoing table:

mysql> select * from ilike;
+-----------+------------+----------+
| likebread | likecheese | likemilk |
+-----------+------------+----------+
| girl      | boy        | girl     |
| boy       | girl       | boy      |
| boy       | boy        | boy      |
| boy       | boy        | girl     |
| boy       | boy        | boy      |
| boy       | girl       | boy      |
| girl      | boy        | girl     |
| boy       | girl       | girl     |
| boy       | boy        | girl     |
| girl      | boy        | boy      |
+-----------+------------+----------+
10 rows in set (0.00 sec)

I'd like count and group the data in the individual columns, so that the end result is a table that shows the counts for boys and girls for each respective column as in:

        +-----------+------------+----------+
        | likebread | likecheese | likemilk |
+-------+-------+------------+--------------+
| boys  | 7         | 7          | 5        |
| girls | 3         | 3          | 5        |
+-------+-----------+------------+----------+

Any assistance appreciated!

Upvotes: 0

Views: 219

Answers (1)

user359040
user359040

Reputation:

Here's one way:

select g.gender,
       sum(case when likebread=g.gender then 1 end) likebread,
       sum(case when likecheese=g.gender then 1 end) likecheese,
       sum(case when likemilk=g.gender then 1 end) likemilk
from (select 'girl' gender union select 'boy') g
cross join ilike
group by g.gender

Result

| GENDER | LIKEBREAD | LIKECHEESE | LIKEMILK |
----------------------------------------------
|    boy |         7 |          7 |        5 |
|   girl |         3 |          3 |        5 |

See the demo

Upvotes: 1

Related Questions