Post reply

Get one row from query

  • June 7, 2011 at 10:08 am

    #239576

    Hi,

    (SELECT COUNT(ID) AS TCount from dbo.Listing where StatusPriorMonth ='Active' )

    SELECT COUNT([ActiveToR]) AS [ActiveToR] from #AllLocations where [ActiveToR] = 1

    SELECT COUNT([ActiveToC]) AS [ActiveToC] from #AllLocations where [ActiveToC] = 1

    I want just one row from the query above.

    TCount ActiceToR ActiveToC

    3691 66 14

    Please help

  • June 7, 2011 at 10:54 am

    #1335168

    PSB (6/7/2011)

    Hi,

    (SELECT COUNT(ID) AS TCount from dbo.Listing where StatusPriorMonth ='Active' )

    SELECT COUNT([ActiveToR]) AS [ActiveToR] from #AllLocations where [ActiveToR] = 1

    SELECT COUNT([ActiveToC]) AS [ActiveToC] from #AllLocations where [ActiveToC] = 1

    I want just one row from the query above.

    TCount ActiceToR ActiveToC

    3691 66 14

    Please help

    Try this

    SELECT COUNT(L.ID) AS TCount, ToR.ActiveToR,ToC.ActiveToC

    from dbo.Listing L

    CROSS APPLY (SELECT COUNT([ActiveToR]) AS [ActiveToR] from #AllLocations where [ActiveToR] = 1) ToR

    CROSS APPLY (SELECT COUNT([ActiveToC]) AS [ActiveToC] from #AllLocations where [ActiveToC] = 1) ToC

    where L.StatusPriorMonth ='Active'

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 7, 2011 at 11:54 am

    #1335219

    Thanks.

  • June 7, 2011 at 12:22 pm

    #1335237

    ... or this (it will only hit the #AllLocations table once, not twice. However, it will scan the entire table. Previous query will take advantage of indexes if available on the ActiveToR/ActiveToC columns.)

    SELECT COUNT(L.ID) AS TCount,

    ToRC.ActiveToR,

    ToRC.ActiveToC

    FROM dbo.Listing L

    CROSS APPLY (SELECT SUM(CASE WHEN [ActiveToR] = 1 THEN 1 ELSE 0 END) AS [ActiveToR],

    SUM(CASE WHEN [ActiveToC] = 1 THEN 1 ELSE 0 END) AS [ActiveToC]

    FROM #AllLocations) ToRC

    WHERE L.StatusPriorMonth ='Active'

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 7, 2011 at 2:32 pm

    #1335324

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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