group by multiple columns in SQL/PHP, where both columns can include the same data

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 />\n";
}

As in the post, desired output is a list of all unique words used in either/both column and not repeated, eg: blue (2) red (3) green (2) yellow (1) — R2D2, Feb 18 '15 at 13:33
SELECT DISTINCT(category) FROM table UNION SELECT DISTINCT(subcategory) FROM table; — Surya, Feb 18 '15 at 13:41

Bang · Accepted Answer · 2015-02-18T13:56:28.160

2

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 />\n";
}

edited Feb 18 '15 at 13:56

answered Feb 18 '15 at 13:34

Bang

919
4
11

This looks promising, and does give me a unique list as desired. What does "as t GROUP" do? I have never seen that before! Also, how do I reference the count? Outputting $category ($count) give me, eg, blue (), with no count? – R2D2 Feb 18 '15 at 13:44
"t" is just an alias it's not linked with the GROUP BY. Where is your php ? – Bang Feb 18 '15 at 13:48
added my current php code to the bottom of my question above, as I cant add code to a comment block. – R2D2 Feb 18 '15 at 13:53
updated ! I don't expect extract will create $count from $row['count(*)'] – Bang Feb 18 '15 at 13:59
Have this all working now. Thank you for your time and effort. – R2D2 Feb 18 '15 at 14:52

group by multiple columns in SQL/PHP, where both columns can include the same data

1 Answers1

Linked