Multiple SQL queries in the same select?

Question

I am trying to combine multiple selects in one query to use as little data as possible. I have this sql table (example)

id category status
1   test1    A
2   test2    B
3   test1    A
4   test3    B
5   test1    C

First of all i want to select how many rows there is with the same category.

SELECT category, COUNT(category) FROM test GROUP BY category

Then i would like to count the status in each category. I would do this with this query.

SELECT status, COUNT(status) FROM test WHERE category = 'test1' GROUP BY STATUS

So i want one column with total and then each categorys number of status. Can i somehow combine these? Is that even possible or do i just have to realize that I have to get the data multiple times to have the right result?

Answer 1

You can try to GROUP BY category and by status and use WITH ROLLUP to get aggregate values:

SELECT category, status, count(*)
FROM test
GROUP BY category, status WITH ROLLUP

The result will look like this:

 category | status | count(*)
----------+--------+----------
 test1    | A      | 2
 test1    | C      | 1
 test1    | NULL   | 3
 test2    | B      | 1
 test2    | NULL   | 1
 test3    | B      | 1
 test3    | NULL   | 1
 NULL     | NULL   | 5     

If you ignore the rows containing NULLs, the rest is the regular GROUP BY category, status. There are 2 entries having category = 'test1' AND status = 'A', one entry having category = 'test1' AND status = 'C' and so on.

The third row of the result (category = 'test1', status = NULL, count(*) = 3) summarizes the rows having category = 'test1'. It computes count(*) for all the rows having category = 'test1' no matter what value they have in column status. In a similar way there are computed the summary rows for category = 'test2' and category = 'test3'.

The last row is the summary for the entire table. count(*) = 5 includes all the rows, no matter what value they have in columns category and status.

Answer 2

You can run your second query for all categories at once like this:

mysql> select category, status, count(*) from foo group by category, status;
+----------+--------+----------+
| category | status | count(*) |
+----------+--------+----------+
| test1    | A      |        2 |
| test1    | C      |        1 |
| test2    | B      |        1 |
| test3    | B      |        1 |
+----------+--------+----------+
4 rows in set (0.39 sec)

And then you could compute the category-wide count by summing up all its rows. If you really want that too as part of the same query, you could do this:

mysql> select foo.category, status, count(*), cat_count
    -> from foo
    -> inner join (select category, count(*) cat_count from foo group by category) x
    -> on x.category = foo.category
    -> group by foo.category, status;
+----------+--------+----------+-----------+
| category | status | count(*) | cat_count |
+----------+--------+----------+-----------+
| test1    | A      |        2 |         3 |
| test1    | C      |        1 |         3 |
| test2    | B      |        1 |         1 |
| test3    | B      |        1 |         1 |
+----------+--------+----------+-----------+
4 rows in set (0.00 sec)