[RESOLVED] Query to Count Rows based on a Field Value

shawjames

I want to select from my products table so that I can display the total count of rows for each product status. Here are the columns:

productID - product number (not needed for this)
category - category number
inventory - how many in stock
status - product status: Yes, No or Pre

I want a query that will group by category number and show the sum of Inventory in that group for each of the 3 product status. So, it would show each row:

category#, total inventory with status=Yes, total inventory with status=No, total inventory with status=Pre

I can easily do this with 3 separate queries, but I would like to do it with a single query. Can this be done?

bogu

It depends how you want your results, this can be done two ways ...

SELECT
	t.`category`,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'yes' AND `category` = t.`category`) as status_yes,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'no' AND `category` = t.`category`) as status_no,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'pre' AND `category` = t.`category`) as status_pre
FROM `table` t
GROUP BY t.`category`

SELECT
	`category`, `status`, SUM(`inventory`) as inventory
FROM `table`
GROUP BY `category`, `status`

shawjames

bogu;10898519 wrote:

It depends how you want your results, this can be done two ways ...

SELECT
	t.`category`,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'yes' AND `category` = t.`category`) as status_yes,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'no' AND `category` = t.`category`) as status_no,
	(SELECT SUM(`inventory`) FROM `table` WHERE `status` = 'pre' AND `category` = t.`category`) as status_pre
FROM `table` t
GROUP BY t.`category`

Thank you, this is the one that would work. However how would I do this if 'category' is actually a function of the productID? Right now, I have it as LEFT(productID,3) as category

bogu

U can use alias: http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Weedpacket

Actually, both would work, and the second one would be more efficient; you'd just have to take the three results and put them in three variables instead of having them in three variables to begin with.

shawjames wrote:
However how would I do this if 'category' is actually a function of the productID?

That would involve creating a separate query for each individual productID. You might be able to use a UNION to combine them all, but they'd still be separate queries. The real answer is that your database schema is seriously wonky.

shawjames

SELECT
category, status, SUM(inventory) as inventory
FROM table
GROUP BY category, status

Thank you, I will make this one work. I can use my LEFT operation with this one. The other one blew my server :eek:

Thanks guys!