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?

Paul A Jungwirth · Accepted Answer · 2016-02-21 21:22:20Z

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.

Paul A Jungwirth · Accepted Answer · 2016-02-21 21:18:17Z

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)

Kamil Gosciminski · Accepted Answer · 2016-02-21 21:18:04Z

0

Unfortunately MySQL does not support window functions.

One way would be to get your status counts for each category in one query:

SELECT
  category,
  status,
  COUNT(*) AS status_count
FROM
  test
GROUP BY
  category, status

And then INNER JOIN information about count for categories to it like that:

SELECT
    a.*, b.category_count
FROM (
    SELECT
      category,
      status,
      COUNT(*) AS status_count
    FROM
      test
    GROUP BY
      category, status
    ) a
    INNER JOIN ( SELECT category, COUNT(*) AS category_count FROM test GROUP BY category ) b ON
        a.category = b.category

answered Feb 21, 2016 at 21:18

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Collectives™ on Stack Overflow

Multiple SQL queries in the same select?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related