grateful

  • 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.

  • 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/

  • Thanks alot! Nop not school stuff

    Ill try this when i get the time =)

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

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