Post reply

Getting the Results witout using SubQuery

  • April 27, 2009 at 12:54 am

    #95478

    I Have a Table like

    ScheduleuserId UseridStatus

    1 1 0

    1 2 1

    1 3 2

    1 4 0

    1 5 1

    1 6 0

    1 7 0

    1 8 1

    1 9 0

    I need output as

    ScheduleUserId Attempted NotAttempted Completed

    1 3 5 1

    NotAttempted means status is 0

    Attempted means status is 1

    Completed means status is 2

    But I Need the Result without using SubQuery Or Using three joins...

  • April 27, 2009 at 3:47 am

    #984236

    Why not the subquery?

    Is it a Quiz...:w00t:

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • April 27, 2009 at 5:10 am

    #984259

    If u use the Sub query its killing the Performance.. So..;-)

  • April 27, 2009 at 5:21 am

    #984262

    [Code]

    DECLARE @YourTable TABLE

    (ScheduleuserId INT,Userid INT, Status INT)

    INSERT INTO @YourTable

    SELECT 1, 1, 0 UNION ALL

    SELECT 1, 2, 1 UNION ALL

    SELECT 1, 3, 2 UNION ALL

    SELECT 1, 4, 0 UNION ALL

    SELECT 1, 5, 1 UNION ALL

    SELECT 1, 6, 0 UNION ALL

    SELECT 1, 7, 0 UNION ALL

    SELECT 1, 8, 1 UNION ALL

    SELECT 1, 9, 0

    SELECT * FROM @YourTable

    SELECT

    ScheduleuserId,

    SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) as [Attempted],

    SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) as [NotAttempted],

    SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) as [Completed]

    FROM @YourTable

    GROUP BY ScheduleuserId

    [/code]

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • April 27, 2009 at 9:16 pm

    #984815

    Thanks Christopher Stobbs

  • April 27, 2009 at 10:50 pm

    #984833

    ningaraju.n (4/27/2009)

    If u use the Sub query its killing the Performance.. So..;-)

    Not always true if the subqueries only use "=". Anyway, looks like Chris get's the hat-trick on this one. Nicely done, Chris.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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