• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

Query to Combine detail with sum

here is some example data from my order detail table    (ms sql server 2008)

orderid   order month    Order Year       Item             Order Amount
1               Jan                           2015            Shoes           23.50
2               Jan                           2015            Gloves          34.25
3               Feb                          2015            Socks            15.33
4               Feb                          2015            Shirts             17.90

Trying to write a query that will list the detail level plus the order amount for the month and year



Using the above  (jan 2015 total is 57.75.....feb 2015 total is 33.23)

I would like following results

orderid   order month    Order Year       Item             Order Amount   MonthOrderTotal
1               Jan                           2015            Shoes           23.50                   57.75
2               Jan                           2015            Gloves          34.25                   57.75
3               Feb                          2015            Socks            15.33                   33.23    
4               Feb                          2015            Shirts             17.90                  33.23
0
johnnyg123
Asked:
johnnyg123
  • 2
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Create a subquery to get the monthly total, then join on the detail table via month and year...
SELECT od.orderid, od.ordermonth, od.orderyear, od.item, od.amount, odmonth.MonthOrderTotal 
FROM orderdetail od 
   JOIN (
      SELECT OrderMonth, OrderYear, SUM(OrderAmount) as MonthOrderTotal
      FROM orderdetail 
      GROUP BY OrderMonth, OrderYear) odmonth ON od.ordermonth = odmonth.ordermonth AND od.orderyear = odmonth.orderyear
ORDER BY orderid

Open in new window


btw I have an image and code-heavy demo out there called SQL Server GROUP BY Solutions where point 5 illustrates this.
0
 
PadawanDBAOperational DBACommented:
I personally like window functions, so here ya' go:

select
	*,
	sum( orderAmount ) over( partition by orderYear, orderMonth ) as MonthlyOrderTotal
from
	OrderDetail
order by
	orderid;

Open in new window

0
 
johnnyg123Author Commented:
Hi Jim

Thanks so much for the response!

Exactly what I was looking for

The Link to the demo was awesome as well...that will definitely come in handy for future reference

Wish I could award more than 500 points!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That's fine.  PadawanDBA's answer works too, and is less code, assuming you're on 2008 or greater.  

Since I wrote an article that didn't use that windowing function, I have a hammer, everything looks like a nail, you get the idea.
0
 
johnnyg123Author Commented:
I realize that since I using sql server 2008

PadawanDBA's will work

I do like Jim's approach better because we do have some servers that are not 2008 so....

Also, wanted to give extra credit for the link
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now