在SQL / PHP中按多列分组，其中两列可以包含相同的数据

Question

I have a challenge in php/mySQL, that I cant find any other examples of, and is proving a bit of a challenge.

I have a table that has "category" and a "subcategory" columns. Both could contain the same data, or could be blank, eg:

--------------------------
| category | subcategory |
--------------------------
|   blue   |             |
|   blue   |    green    |
|  green   |     red     |
|  yellow  |     red     |
|    red   |             |
--------------------------

I want to group and filter these results to show a simple list of all categories used, eg:

blue (2)
red (3)
green (2)
yellow (1)

I can group and sort by one column no problem, but cant see a way how to do this including two columns.

SELECT category,subcategory FROM table GROUP BY category,subcategory

All the examples I can see online involve grouping by two columns, where the columns are different and contain different data, eg: firstname, lastname, so I dont think this is a duplication!

Thanks in advance for any thought or assistance with this!

---

edited to show current code to @bang, as per comments below:

$query = "SELECT category, count(*) FROM ( SELECT category from ae_test UNION ALL SELECT subcategory as 'category' from ae_test ) as t GROUP BY category";
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {
extract($row);
echo "$category ($count)<br />
";
}

Answer 1

Then try this :

SELECT category, count(*)
FROM ( 
    SELECT category from table
    UNION ALL
    SELECT subcategory as 'category' from table
) as t GROUP BY category;

The first select get your category and the second your subcategory.

I use UNION ALL to get all data even if they are duplicated.

In your php please never use extract :

$query = "SELECT category, count(*) FROM ( SELECT category from ae_test UNION ALL SELECT subcategory as 'category' from ae_test ) as t GROUP BY category";
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {

     echo "$row['category'] ($row['count(*)'])<br />
";
}