SQLTeam.com | Weblogs | Forums

Getting summed items


#1

Hi There,
I'm in need of your help again.

I have a data table with PartId, RentalDate, RentalCost
I need to sum in various ways based on the rental date. I.E.
Get total sums for each PartId rented in October
AAD11 £ 302.00
BAR233 £ 626.00
CDN112 £ 104.45
Get total sums for each PartId rented in 2015
AAD11 £ 15,122.00
BAR233 £ 19,728.00
CDN112 £ 10,460.15
Get total sums for each month of 2015
January £ 901.26
February £ 678.80
March £ 877.10
...

How would I go about that?
Sorry if it's too much to ask.

Thanks for your help.

Best Regards,

Steve.


#2
select partid,
   sum(rentalcost) over(partition by month(rentaldate)) as by_month,
  sum(rentalcost) over(partition by year(rentaldate)) as by_year

#3

Hi There,

Thanks for the reply.
That seems to work but I needed them as individual sums. I.E.
If I choose that I would like a yearly total for all the rentalids show that

If I choose that I would like a monthly total for all the rentalids show that

If I choose that I would like a total for all rentalids in October show that

Thanks for your help, I appreciate it.

Best Regards,

Steve.


#4

OK -- Are you planning to write a stored procedure to handle the logic? If so, just build the individual queries and wrap them in IF...BEGIN...END statements.

If you're still unclear, please post your table definitions (as CREATE TABLE statements) and samples of the output you want.