MySQL：计算每个字段中的不同值

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!

Answer 1

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.