Sum the MAX
-
SELECT MAX(month_end),sum(num_units) from active_Units_Static where month_end >= '1/1/2002' and month_end <= '12/31/2002'
I want the sum of the month_end on the largest date. This select statement sums everything and not just the max records. IS it possible to get the sum of the max'd records. If not, then I could hard code the years by terminal number.
Matt
-
Will something like this work for you:
SELECT MAX(month_end),sum(num_units) from active_Units_Static where month_end = (select max(month_end) from active_Units_Static)
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Hmm no, I need it per year by terminal summed up.Pretty much I just want a sum of the number as its part of a bigger query.
so if w/o the sum portion I get
termnum total Units
123 50
456 100
678 1
What I am looking for is:
total_units
151
Matt
Edited by - matt101 on 12/16/2003 1:13:41 PM
-
Have you considered using datepart(yy,month_end) somewhere in your query, and/or the group by clause possible
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Yes, I have I am only working with 1 year for now. Once I get the query working to actually show me the number I am looking for, I will be implementing that and it should work with ease but right now I am focusing on how to get the max date per terminal summed up which is the real issue at the moment.
Matt
-
Matt, something like...
SELECT Terminal, Key_month_end, SUM(num_units)
FROM active_Units_Static A
JOIN (
SELECT Terminal, MAX(month_end) as Key_month_end
FROM active_Units_Static
where month_end >= '1/1/2002' and month_end <= '12/31/2002'
GROUP BY Terminal
) B
ON A.Terminal = B.Terminal
AND A.month_end = B.Key_month_end
GROUP BY A.Terminal, B.Key_month_endIf you want to group on an aggregate you may need to query the table twice (as my example).
Once you understand the BITs, all the pieces come together
-
Wouldn't a classic group by work?
I don't know your data types - it would help next time if you show a create table script.
Assuming month_end as datetime (why would every day be called month_end?):
--note: it could be done in one select, but it's more clear & easier to modify when split
--create temp table
create table #t
(me tinyint,
yr int,
nu money
)
--select, group by date, get year in case needed in future
insert #t
select month(month_end), year(month_end), sum(num_units) from active_Units_Static
where year(month_end) = 1996
group by month(month_end),year(month_end)
--for last day of month
select dateadd(day,-1,dateadd(month,me,'01/01/' + cast(yr as char(4)))) as LastDay,
nu as [Sum of units]
from #t
order by LastDay
-- for last day with data
select max(month_end) as LastDay, nu as [Sum of units]
from #t t join active_Units_Static a on
t.yr = year(month_end) and t.me = month(month_end)
group by nu
order by LastDay
Hope that helps!
The only normal people are those you don't know well - Oscar Wilde
Data: Easy to spill, hard to clean up!
-
Wow - the internet went down for a few minutes - and when my reply posts there's already 4 more!
Data: Easy to spill, hard to clean up!
-
Thanks Thomas, that worked and now thats cool how that works.
Stubob: Thanks for your idea, its a bit long winded and no I cant just use a group by because the SUM portion sums everything between the date range and not just the max which is why posted here. I did take your Year(month_end) because it made my code slightly easier to read considering this is part of of a much larger select statement.
Matt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply