Post reply

subquery returning multiple results

  • February 25, 2012 at 9:49 am

    #251429

    hi all,

    i have a table with below columns:

    studentname, createdby, updatedby

    sample data:

    s1, user1, user3

    s2, user3, user4

    s3, user2, user3

    can someone pls help me in writing a query that shld return the following output:

    username | no. of records created | no. of records update

    user1 | 1 | 1

    user3 | 1 | 2

    user4 | 0 | 1

    thanks,

    bhavtosh

  • February 25, 2012 at 11:53 am

    #1451729

    I'm expecting there's a users table...

    SELECT

    t2.usr,

    SUM (CASE WHEN t1.createdby =t2.usr THEN 1 ELSE 0 END ) AS created,

    SUM (CASE WHEN t1.updatedby =t2.usr THEN 1 ELSE 0 END ) AS updated

    FROM @tbl t1

    LEFT OUTER JOIN users t2 ON (t1.createdby =t2.usr OR t1.updatedby =t2.usr)

    GROUP BY t2.usr


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 25, 2012 at 12:43 pm

    #1451749

    Guessing again, it seems like the OP is quite happy with the answer received at msdn


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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