Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query to sum data Expand / Collapse
Author
Message
Posted Saturday, December 7, 2013 6:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:20 AM
Points: 49, Visits: 214
I have a table with database sizes in it that tracks the size of the database for every day of the past year:

Table:
Server Database Date Size
CS1 cust 1/1/2013 1200
CS1 cust 1/2/2013 1210
CS1 prog 1/1/2013 800
CS1 prog 1/2/2013 820
.
.
CS1 cust 8/1/2013 1700
CS1 cust 8/2/2013 1800
CS1 prog 8/1/2013 1000
CS1 prog 8/2/2013 1020

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)
Post #1520847
Posted Saturday, December 7, 2013 7:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:20 AM
Points: 49, Visits: 214
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

Post #1520849
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse