Difficult Count/Group By query

Question

Difficult Count/Group By query

Martin Davies-966230

SSC-Addicted

Points: 447
More actions
August 21, 2010 at 9:14 am

#222972

Here are stripped down versions of the tables/columns involved in my problem:
[font="Courier New"]
tORDERS
- order_id
tORDER_ITEMS
- order_id
- stock_id
tSTOCK
- stock_id
- stock_category
[/font]
I want to create a count / group by query to show how many orders have items on them for each stock category.
So for example i might end up something like with:
[font="Courier New"]
Electrical 5
Produce 2
Frozen 3
[/font]
I can create a query that returns the number of Order Items for each stock category,
but what I really want is the number of orders. I just can't work out how to do it.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95450 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Also, you posted in the SQL 7/2000 forum. Please verify which version that you are using.

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

Martin, to Waynes point, you really need to give us more information to go on. Without it, we have to make assumptions about what you have, and what you want. However, its a slow Friday and all, so I made some assumptions to produce your desired output as stated. Look at how I have included the things requested in the above post. Now, since I had to put some effort in to doing that for you, I'll ask you to put some effort in to understanding it, and thus the lack of comments. Let us know if this helps you grasp your problem.

IF OBJECT_ID('TempDB..#orders','u') IS NOT NULL

DROP TABLE #orders

CREATE TABLE #orders

(

ORDER_ID INT

)

GO

INSERT INTO #orders

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

IF OBJECT_ID('TempDB..#order_items','u') IS NOT NULL

DROP TABLE #order_items

CREATE TABLE #order_items

(

ORDER_ID INT NOT NULL,

STOCK_ID INT NOT NULL,

QUANTITY INT NOT NULL

)

GO

INSERT INTO #order_items

SELECT 1,1,3 UNION ALL

SELECT 1,3,2 UNION ALL

SELECT 2,1,1 UNION ALL

SELECT 2,2,3 UNION ALL

SELECT 2,3,5 UNION ALL

SELECT 3,1,2 UNION ALL

SELECT 4,1,2 UNION ALL

SELECT 5,1,6 UNION ALL

SELECT 5,2,4 UNION ALL

SELECT 5,3,3

IF OBJECT_ID('TempDB..#stock','u') IS NOT NULL

DROP TABLE #stock

CREATE TABLE #stock

(

STOCK_ID INT IDENTITY(1,1),

STOCK_CAT VARCHAR(20) NOT NULL

)

INSERT INTO #stock

SELECT 'Electrical' UNION ALL

SELECT 'Produce' UNION ALL

SELECT 'Frozen'

SELECT

t1.STOCK_CAT,

ORDER_COUNT = COUNT(t1.stock_cat)

FROM

(

SELECT

oi.ORDER_ID,

s.STOCK_CAT,

oi.QUANTITY

FROM #order_items oi INNER JOIN #stock s

ON oi.stock_id = s.stock_id

) t1

GROUP BY t1.stock_cat

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.