Items and categories (1:N) How to get number of total and total active items per category in single SQL query?

items

item_id
title
active = 1/0

items_categories_map

item_id
category_id

I need to get result

category_id
items_total (any value of items.active)
items_active (items.active = 1)
items_inactive (items.active = 0)

Is it possible to get such result in开发者_如何学编程 a single SQL query without using UNION?

Thank you!

Use a template such as the following...

COUNT(items.active) AS items_total,
SUM(items.active)   AS items_active,
SUM(1-items.active) AS items_inactive

Or when you have values that are not 1 or 0...

SUM(CASE WHEN items.active = 0 THEN 1 ELSE 0 END) as items_inactive

To add to Dems: if you want to group by and have grand total as well use:

select items.id
 , COUNT(items.active) AS items_total
 , SUM(ifnull(items.active,0)) AS items_active
 , SUM(1-ifnull(items.active,0)) AS items_inactive
FROM items
GROUP BY items.somefield WITH ROLLUP

+------+-------------+---------------+----------------+
| id   | items_total | items_active  | items_inactive |
+------+-------------+---------------+----------------+
| 1    | 2           | 1             | 1              |
| 2    | ....
| null |100          | 60            | 40             |<<- grand total
+------+-------------+---------------+----------------+

with rollup you'll get a grand total row at the bottom.

ifnull needed?
If items.active can be null, you'll need a ifnull() in there to prevent the sum from evaluating to null and messing up your result.