Post reply

Help with subquery

  • November 9, 2009 at 4:03 pm

    #211948

    Hi,

    I'm trying to write a query that will retrieve 4 values... a person's name, total number of records for them, number of records greater than 90 days old, and number of records less than 90 days old. Result should be something like this:

    Bob, 120, 20, 100

    I've written the query below but I'm getting an error in Management Studio saying:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Which has left me stumped...

    SELECT Manager, COUNT(Call_Number) AS Total,

    (SELECT Manager, COUNT(Call_Number) AS Expr1

    FROM CallSummary

    WHERE (Days_Old > 90) AND (Call_Status = 'O') Group By Manager) AS GT90,

    (SELECT Manager, COUNT(Call_Number) AS Expr1

    FROM CallSummary AS CallSummary_2

    WHERE (Days_Old < 90) AND (Call_Status = 'O')Group By Manager) AS LT90

    FROM CallSummary AS CallSummary_1

    WHERE (Call_Status = 'O')

    GROUP BY Manager

  • November 9, 2009 at 4:50 pm

    #1077029

    Try this:

    SELECT Manager,

    COUNT(Call_Number) AS Total,

    SUM(CASE WHEN (Days_Old > 90) AND (Call_Status = 'O') THEN 1 ELSE 0 END) as GT90,

    SUM(CASE WHEN (Days_Old < 90) AND (Call_Status = 'O') THEN 1 ELSE 0 END) as LT90,

    FROMCallSummary AS CallSummary_1

    WHERE Call_Status = 'O')

    GROUP BY Manager

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 10, 2009 at 2:46 pm

    #1077549

    Thanks John!

    That worked great.

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

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