SQL query to sum data

• jamie_collins

  Ten Centuries

  Points: 1192

  More actions

  December 7, 2013 at 6:20 pm

  #302119

  I have a table with database sizes in it that tracks the size of the database for every day of the past year:

  Table:

  ServerDatabaseDate Size

  CS1cust1/1/20131200

  CS1cust1/2/20131210

  CS1prog1/1/2013800

  CS1prog1/2/2013820

  .

  .

  CS1cust8/1/20131700

  CS1cust8/2/20131800

  CS1prog8/1/20131000

  CS1prog8/2/20131020

  I have a query that gives me the highest size per month for a specific database:

  SELECT MONTH(Date) AS Month, max(Size) AS Size

  FROM DatabaseSize_Archive

  WHERE Server = 'CS1'

  AND Database = 'cust'

  GROUP BY MONTH(Date)

  ORDER BY Month DESC

  Returns:

  Month Size

  1 1210

  8 1800

  But I need a query that gets me the Sum of that highest size for the server per month...

  Results:

  Month Size

  1 2030

  8 1840

  Obviously changing MAX in the above query to just SUM gets me the sum of every size and that's not what I need.

  Any assistance would be appreciated.

  Thanks

  (Sorry for the formatting, not sure how to insert table data)

• jamie_collins

  Ten Centuries

  Points: 1192

  More actions

  December 7, 2013 at 7:44 pm

  #1671743

  Got it:

  with cte as

  (

  SELECT Database, MONTH(Date) AS Month, max(Size) AS Size

  FROM DatabaseSize_Archive

  WHERE Server = 'CS1'

  GROUP BY MONTH(Date)

  )

  SELECT Month, SUM(Size)

  FROM cte

  GROUP BY Month

  ORDER BY Month DESC