CODEWITHSUNDEEP
mysqlsqlselectcount
hynick
hynick

Reputation: 785

MySQL GROUP BY and COUNT over Multiple Columns

I have a MySQL Table as follows:

+----+-------+-------+-------+-------+-------+
| ID |  MON  |  TUE  |  WED  |  THU  |  FRI  |
+----+-------+-------+-------+-------+-------+
|  0 |  Bike |  Bike |  Walk |  Bike |  Car  |
|  1 |  Car  |  Car  |  Car  |  Bus  |  Car  |
|  2 |  Bus  | Train |  Bus  |  Bus  | Train |
|  3 |  Car  |  Car  |  Car  |  Walk |  Car  |
+----+-------+-------+-------+-------+-------+

How would I group by and count all days, to get the total modes of each transport over the week. For example:

+--------+-------+
|  MODE  | COUNT |
+--------+-------+
|  Bike  |   3   |
|   Bus  |   4   |
|   Car  |   9   |
|  Train |   2   |
|  Walk  |   2   |
+--------+-------+

I have tried using:

SELECT COUNT(*), Mon 
FROM transport 
GROUP BY Mon, Tue, Wed, Thu, Fri

But this creates a new group for each unique value in each day.

Upvotes: 4

Views: 718

Answers (3)

Vivek V H
Vivek V H

Reputation: 1

SELECT mode, COUNT(*)FROM(SELECT mon AS mode FROM transport UNION ALL
          SELECT tue AS mode FROM transport UNION ALL
          SELECT wed AS mode FROM transport UNION ALL
          SELECT thu AS mode FROM transport UNION ALL
          SELECT fri AS mode FROM transport) t
GROUP BY mode

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you have a separate table of modes, you can also do:

select m.mode, count(*)
from modes m join
     transport t
     on m.mode in (t.mon, t.tue, t.wed, t.thu, t.fri)
group by m.mode;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311073

One way to do this is to produce a subquery that selects the transport mode in one column using the union all operator, and then counting the occurrences:

SELECT   mode, COUNT(*)
FROM     (SELECT mon AS mode FROM transport UNION ALL
          SELECT tue AS mode FROM transport UNION ALL
          SELECT wed AS mode FROM transport UNION ALL
          SELECT thu AS mode FROM transport UNION ALL
          SELECT fri AS mode FROM transport) t
GROUP BY mode

Upvotes: 4

Related Questions