Totals question

[tiredhero16]

Say I have a table with the product ID and product category, and units in stock. I would like for each individual unit to grouped so that access displays the total number of products in each category. I have been at this for awhile, and I am not having any luck.





I would like the total number of products per each category (ex. Windchimes-4, Birdhouses-3)

Thank you so much in advance.

[isladogs]

Replace TableName with whatever your table is called
Check I've got the field names correct

SELECT TableName.[Product Category], Count(TableName.[Product Category]) AS [Total Products]
FROM TableName
GROUP BY TableName.[Product Category], TableName.[Units in Stock]
ORDER BY TableName.[Product Category];

Recommend you do not use spaces in field or table names - then the [] brackets wouldn't be needed

[tiredhero16]

My mistake for not being very clear. What I intended to say was that I am trying to sort by how many items per category are not in stock (0). I think that is easier than the SQL code you provided, but I still can't figure it out.

[isladogs]

Slightly confused as I got a notification email saying:

That works! Thanks!

Assume you edited that afterwards....

My mistake for not being very clear. What I intended to say was that I am trying to sort by how many items per category are not in stock (0). I think that is easier than the SQL code you provided, but I still can't figure it out.

I think you mean COUNT items WHERE not in stock i.e. Units in Stock = 0

SELECT Count(TableName.[Product Category]) AS [Total Products]
FROM TableName
WHERE TableName.[Units in Stock] = 0;

For the data shown, this gives Total Products = 8

If you want GROUPING as well

SELECT TableName.[Product Category], Count(TableName.[Product Category]) AS [Total Products]
FROM TableName
WHERE TableName.[Units in Stock] = 0
GROUP BY TableName.[Product Category]
ORDER BY TableName.[Product Category];

This gives Birdhouses = 3 ; Planters = 1 ; Windchimes = 4

[tiredhero16]

Great. This time I got it. No edits this time. Thanks!