Post reply

Looking for clean way to aggregate the same column multiple times in my query

  • July 23, 2013 at 3:00 pm

    #277789

    Looking for tips on how best to approach the following query - 10million rows. Subquery selects, sum, CTE, over with Partionioning, etc . Is there a better way to do this in 2012? using Windowing - My code base is 2008.

    SELECT

    [DateofScan]

    ,[DeviceType]

    ,count ofdistinct [PlugInID]

    ,count ofdistinct [PlugInID] that are Critical

    ,count ofdistinct [PlugInID] where [VulNotDate] is NULL --'1900-01-01'

    ,Count ofdistinct (PlugInID] where [VulNotDate] <= 90 daysofSan

    ,Count ofdistinct (PlugInID] where [VulNotDate] > 90 daysofSan

    FROM [TCH_Metrix].[dbo].[VulMgmt]

    GROUP BYDateofScan, DeviceType


    John Zacharkan

  • July 23, 2013 at 3:07 pm

    #1635145

    can you use the trick of a SUM(CASE...

    not sure if you need a count, or the coutn distinct for each item:

    SELECT

    [DateofScan]

    ,[DeviceType]

    ,count([PlugInID])

    ,SUM(CASE WHEN Critical = 'Y' THEN 1 ELSE 0 END) AS Critical

    ,SUM(CASE WHEN [VulNotDate] is NULL THEN 1 ELSE 0 END) AS NotDates

    ,SUM(CASE WHEN [VulNotDate] <= 90 THEN 1 ELSE 0 END) AS daysofSan

    ,SUM(CASE WHEN [VulNotDate] > 90 THEN 1 ELSE 0 END) AS daysofSan

    FROM [TCH_Metrix].[dbo].[VulMgmt]

    GROUP BY DateofScan, DeviceType

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 23, 2013 at 3:39 pm

    #1635149

    Absolutely - anything is possible I was just looking for clean and efficient.


    John Zacharkan

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

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