MySQL: Count different values in each of the fields

Question

I am not sure if there is no answer here on Stack Overflow, but I did not find exact thing there. So, I have a table with data like this:

id    | sector | type  | level
      |        |       |
1     | TMT    | Long  | First
2     | Energy | Long  | Second
3     | TMT    | Short | Third
4     | Other  | Long  | First
5     | TMT    | N/A   | Sixth
6     | Other  | Short | First

What I need is summary of each different value in each field, like this:

Sector TMT: 3
Sector Energy: 1
Sector Other: 2
Type Long: 3
Type Short: 2
Type N/A: 1
Level First: 3
Level Second: 1
Level Third: 1
Level Sixth: 1

Type and Level have fixed values. Sectors could be dynamic, I mean there is no final list as they are adding with each item.

For now I am selecting all rows and processing it with PHP:

SELECT `sector`, `type`, `level` FROM `table`;

and for example:

<?php
$result['type_long'] = 0;
$result['type_short'] = 0;
$result['type_na'] = 0;
foreach ($rows as $row)
{
    if ($row['type'] == 'Long')
    {
        $result['type_long']++;
    }
    else if ($row['type'] == 'Short')
    {
        $result['type_short']++;
    }
    else if ($row['type'] == 'N/A')
    {
        $result['type_na']++;
    }
}

etc. For 10000 records it's not really fast cause I need to select all rows from database and then do something to them via PHP.

Is there any way I can do this faster with MySQL?

Thanks!

O. Jones · Accepted Answer · 2015-03-24 21:30:35Z

2

This is a straightforward application of the union of several summary queries. A typical summary query would be

SELECT Sector, COUNT(*) cnt
  FROM mytable
 GROUP BY Sector

To get your exact result set you could do something like this:

SELECT CONCAT('Sector ', Sector, ':') item, COUNT(*) cnt FROM mytable GROUP BY Sector
UNION ALL
SELECT CONCAT('Type ', Type, ':') item, COUNT(*) cnt FROM mytable GROUP BY Type
UNION ALL
SELECT CONCAT('Level', Level, ':') item, COUNT(*) cnt FROM mytable GROUP BY Level

If you want this to be optimally fast, you might try adding indexes on the three columns.

MySQL is quite good at handling summary queries with high performance. Even with multiple separate SELECT queries in this UNION, it will perform an order of magnitude better than transferring all your raw data rows from the server to your MySQL program.

edited Mar 24, 2015 at 21:30

answered Mar 24, 2015 at 21:27

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

1 Comment

Paul Denisevich Over a year ago

Good point, thanks. What do you think about its performance as there are 3 SELECTs anyway? To be exact I will need 5 SELECTs to count all this as I have 5 options there (not just sector, type and level). I have indexes on all these fields as well.

Collectives™ on Stack Overflow

MySQL: Count different values in each of the fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related