grateful

Question

grateful

jonas_ljungby

Grasshopper

Points: 18
More actions
June 17, 2015 at 1:08 am

#398609

Hello, i would be so grateful is somone could help me with this.
Table data
Order : Line : Category : Quantity
1 : 1 : Drama : 1
1 : 2 : SciFi : 1
2 : 1 : Drama : 1
2 : 2 : Drama : 4
3 : 1 : Fising : 1
OUTPUT: I would like this output
Quantity : Orders that have that quantity
1 : 1
2 : 1
5 : 1
Quantity : Orders that have that quantity - Drama
5 : 1
The Order with Drama and SciFi sould not be a part of this result.
Just the orders that have only Drama sould be shown in the last output.

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

This looks like homework to me, so I'm not willing to give you the answer but I will try and help you arrive at it on your own.

DECLARE @TABLE AS TABLE

(

[Order] INT,

[Line] INT,

[Category] VARCHAR(10),

[Quantity] INT

);

INSERT INTO @TABLE

SELECT 1, 1, 'Drama', 1

UNION ALL

SELECT 1, 2, 'SciFi', 1

UNION ALL

SELECT 2, 1, 'Drama', 1

UNION ALL

SELECT 2, 2, 'Drama', 4

UNION ALL

SELECT 3, 1, 'Fising', 1;

The above is your sample data inserted into a table called "@TABLE".

So, part one you need to figure out how many orders have a total quantity of particular values. What you want to look at is a SUM quantities, what would you group that by? That result-set wants to be in a SUBQUERY, then you want to do a COUNT of you Order, would would you group that by?

Part two requires more subquery thinking. First, you need a join between @TABLE and itself, where one side of the join contains only those rows with "Drama" and the other side shows only those rows without "Drama". If the join is a success, you want to filter out the rows. This can be done in many ways, but a quick way to show you would be to do this: -

SELECT *

FROM @TABLE a

WHERE a.[Category] = 'Drama'

AND NOT EXISTS ( SELECT 1

FROM @TABLE b

WHERE a.[Order] = b.[Order]

AND b.[Category] <> 'Drama' );

Using that query, or one similar, you can combine it with the workings for part one to figure out your answer to part two.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jonas_ljungby Grasshopper Points: 18 More actions · Answer 2

Thanks alot! Nop not school stuff

Ill try this when i get the time =)