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

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  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

• Lowell

  SSC Guru

  Points: 323527

  More actions

  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!

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  July 23, 2013 at 3:39 pm

  #1635149

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

  
  John Zacharkan