Sum Aggregates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum Aggregates

Author

Topic

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 15:01:24

Hi All,

I have the following scenario. I need to create sums for a field in a view grouped on a per month basis. I tried grouping on our Date field but gives back every single record for that month and not just one total for month. So what is the correct way to do this?

Also how can I take one value in one record and subtract it from another value in different record?

JJ

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-10 : 15:07:42

Do you want each month to be a column in the results set? Post your query for us to have a look.

Raymond

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 15:11:18

you need to calculate the month of your date, and group on that. you can use something like

select Year(date) as Yr, Month(date) as Mnth, Sum(amount)
from Yourtable
group by Year(date), month(date)

as for the math between rows, you can try this:

In 1 set of rows, leave it the amounts positive, in the other set, make them negative (you will need logic to determine this) and then do a SUM() on the amount column and it will come out the way you like hopefully.

i.e.,

select customer, sum(case when type='debit' then Amount else Amount * -1 end) as Balance
from yourtable
group by customer

... where each row has a "type" column of 'debit' or 'credit', and you wish to subtract credits from the debits.

- Jeff

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 16:20:11

Thanks Guys this is a real big help.

How can I calculate a value for the SUM of Last months total for a column - This months SUM total using same Column?

Thanks,

JJ

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 16:30:50

Just to elaborate more on the other post.

Formula is : LastMonthsEnd - ThisMonthsEnd * 7.28

SO first I would sum up the Column to get a total for LastMonthsEnd and then Sum up a total to get thisMonthsEnd and subtract the two somehow. Any Ideas ?

Thanks,

JJ

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 17:48:51

What if I used a stored procedure and put one months sum results into a temp table and another sum months in a another temp table and some how subtracted the two values. How could I do this in a stored procedure?

Thanks,

JJ

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 18:42:46

if "SQL" is a SELECT statement that returns 1 row per Year, Month, with a column called "Amount" (like the one I gave you), you can do this:


select a.Year, a.Month, a.Amount, (a.Amount - isnull(b.amount,0)) as Diff_from_last_month
from (SQL) a
left outer join (SQL) b
on (a.year = b.year and a.month = b.month+ 1) OR
   (a.year = b.year+1 and a.month=1 and b.month=12)

- Jeff

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 20:21:13

Hi Jeff,

Not quite sure what you mean by "if SQL is a SELECT statement"?
and what you gave me is working great in your first reply. Just need a little clarification on what SQL is. Does it represent the result of first SQL statement in first reply? Or does it represent a Table to you here ?

Thanks for all your help,
JJ

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 20:36:21

Do you mean in a Stored Procedure to do this in? Can I do this in a view if I wanted too?

JJ

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 20:49:37

Here is my SQL code:


SELECT a.Yr, a.Mnth, a.PropRec, a.PropRec - ISNULL(b.PropRec, 0) AS Diff_from_last_month
FROM (SELECT YEAR(MR_Date) AS Yr, MONTH(MR_Date) AS Mnth,
 SUM(Prop_Received) AS PropRec,
 SUM(Ammonia_Received) AS TotAmmRec
  FROM dbo.MidnightReadings
  GROUP BY YEAR(MR_Date), MONTH(MR_Date)) a LEFT OUTER JOIN
  (SELECT YEAR(MR_Date) AS Yr, MONTH(MR_Date) AS Mnth,
    SUM(Prop_Received) AS PropRec, SUM(Ammonia_Received) AS TotAmmRec
    FROM dbo.MidnightReadings GROUP BY YEAR(MR_Date), MONTH(MR_Date))  b ON 
 (a.Yr = b.Yr AND a.Mnth = b.Mnth + 1) OR
 (a.Yr = b.Yr + 1 AND a.Mnth = 1 AND b.Mnth = 12)

I just tryed it out. I need to tweak it abit but I think it will do the trick. This is too cool. Can I use this in Access SQL as well in a Query? Is access able to handle this? Thanks a Ton

JJ

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 20:53:31

yeah, sorry, reading it again, it was kind of vague but you figured it out ! nice job ... Access can do this just fine as well, but you need to save the "subqueries" as seperate queries, since you cannot nest your SQL all in 1 statement in MS Access.

- Jeff

JJ
Starting Member

23 Posts

Posted - 2004-03-10 : 21:30:18

Thanks Jeff

You mentioned saving the subqueries for access. Now in the main query how do I call the subqueries to use in main query?

Thanks Again,

JJ

JJ
Starting Member

23 Posts

Posted - 2004-03-11 : 19:16:30

Jeff,

I have another question for you. Now how can I get other calculations all based on a month/year relationship into the same table. I need to generate a report and of course different ways of calculating totals but need to be combined to produce a report. Should I use a stored procedure and dump all the calcs into variables then some how feed that into a temp table? What do you think?

Thanks,

JJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources