SQL query to sum data

  • 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)

  • 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

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

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