MySQL GROUP BY and COUNT over Multiple Columns

Question

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.

Mureinik · Accepted Answer · 2015-07-04 09:01:53Z

4

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

answered Jul 4, 2015 at 9:01

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2015-07-04 12:44:22Z

1

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;

answered Jul 4, 2015 at 12:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Suraj Rao · Accepted Answer · 2021-09-18 08:49:29Z

0

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

edited Sep 18, 2021 at 8:49

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Sep 18, 2021 at 8:43

Vivek V H

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

MySQL GROUP BY and COUNT over Multiple Columns

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related