CODEWITHSUNDEEP
mysqlsubqueryunion
Nick Rogers
Nick Rogers

Reputation: 395

Using column from outer MySQL query in subquery

I have a MySQL query that I would like to optimize a little bit. Here it is:

SELECT `items`.category AS 'Category', 'Without' AS 'Which', COUNT(*) AS 'Count'
FROM `items` 
WHERE `id`=706 
GROUP BY `category`

UNION

SELECT `items`.category AS 'Category', 'With' AS 'Which', COUNT(*) AS 'Count'  
FROM `items` 
WHERE `id`=706 AND `qa_items`.pointsoff=0 
GROUP BY `category`;

What I get is this:

+---------------------------+---------+-------+
| Category                  | Which   | Count |
+---------------------------+---------+-------+
| Category A                | Without |     3 |
| Category B                | Without |     8 |
| Category C                | Without |     4 |
| Category A                | With    |     2 |
| Category B                | With    |     6 |
| Category C                | With    |     4 |
+---------------------------+---------+-------+

But what I would like is this:

+---------------------------+---------+-------+
| Category                  | Without | With  |
+---------------------------+---------+-------+
| Category A                |       3 |     2 |
| Category B                |       8 |     6 |
| Category C                |       4 |     4 |
+---------------------------+---------+-------+

I know I probably have to do a subquery of some sort, but I'm not sure how I would reference what category I am looking for in the subquery. I'm sure there is something simple here I am missing. Can anyone help? Thanks!

Upvotes: 0

Views: 328

Answers (2)

Olivier Coilland
Olivier Coilland

Reputation: 3096

Here you can simply drop the UNION and use instead:

SELECT
    category as Category,
    COUNT(*) as Without,
    SUM(pointsoff = 0) as With
FROM items
WHERE id=706
GROUP BY category;

Note the SUM(boolean condition) pattern. It's a really usefull trick worth remembering as it has some nifty performance-wise usage. Note that you can use any boolean condition you want in it and that it will simply give you the number of rows in the group fulfilling the condition.

Upvotes: 1

Hituptony
Hituptony

Reputation: 2860

Instead of your query... you would want to do something like

SUM(IF(items.Without, 1,0)) as Without, SUM(IF(items.With, 1,0)) as With for the columns

Upvotes: 0

Related Questions