Solved

SQL syntax

Posted on 2011-03-11
3
272 Views
Last Modified: 2012-05-11
Hello All,

I have a report which runs weekly to produce weekly sales report, I want to add another column to get the cumulative sales and show in the new column, I am unable to figure how I can get the all time sales for the same product in the next column
Please can someone advise?
Thanks
SELECT CAT_Orders.OrderDate,	 
 	CAT_Orders.OrderID,  	CAT_Products.ProductName,  		     		 CAT_OrderDetails.Quantity,			
CAT_OrderDetails.Quantity as Cumulative

FROM  CAT_Orders 

	INNER JOIN CAT_OrderDetails ON CAT_Orders.OrderID = CAT_OrderDetails.OrderID 
	INNER JOIN CAT_Products ON CAT_OrderDetails.ProductId = CAT_Products.ProductId
	 

WHERE     (CAT_Orders.PaymentState='1_paid') 
			AND
          (CAT_Orders.OrderIncludeInWeeklySummary is Null)

Open in new window

0
Comment
Question by:newbie27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
jonaska earned 500 total points
ID: 35107813
SELECT CAT_Orders.OrderDate,      
        CAT_Orders.OrderID,     CAT_Products.ProductName,                                CAT_OrderDetails.Quantity,                      
(SELECT SUM(od.Quantity) FROM CAT_OrderDetails od WHERE od.ProductId = CAT_OrderDetails.ProductId) as Cumulative 
 
FROM  CAT_Orders  
 
        INNER JOIN CAT_OrderDetails ON CAT_Orders.OrderID = CAT_OrderDetails.OrderID  
        INNER JOIN CAT_Products ON CAT_OrderDetails.ProductId = CAT_Products.ProductId 
          
 
WHERE     (CAT_Orders.PaymentState='1_paid')  
                        AND 
          (CAT_Orders.OrderIncludeInWeeklySummary is Null)

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 35108834
Hi jonaska

I am only getting for last week orders, I want to show the cumulative sales for all time orders...

I wonder ifyou can still help

Please advise

Thanks

PS: I can open a new ticket if you want.
0
 
LVL 8

Author Comment

by:newbie27
ID: 35108850
I am geting results like this, do you need we can group the products by name?

Thanks
2010-11-22 11:19:33.050	6002	Cupcake Kit	1	1
2010-11-24 16:54:50.567	6007	Cinderella Theatre	1	1
2010-11-25 21:05:33.067	6008	Astronaut Ice Cream	1	1
2010-11-25 21:05:33.067	6008	Guinness World Records  YoYo Set	1	1
2010-11-25 21:05:33.067	6008	Guinness World Records  Playing Card Set	1	1
2010-11-25 21:05:33.067	6008	Guinness World Records  Paper Plane Set	1	1
2010-11-30 11:11:40.173	6010	Film Quote Babygrow - The Force is Strong in This One	1	1
2010-12-01 09:30:52.060	6020	Wooden Acrobatic Monkey	1	1
2010-12-09 18:09:46.757	6039	Fisher Space Pen  Bullet	1	1
2010-12-09 18:09:46.757	6039	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2010-12-09 22:26:37.457	6040	Royal Botanical Candles  	1	1
2010-12-09 22:26:37.457	6040	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2010-12-09 22:26:37.457	6040	Football Ground Street Sign	1	1
2010-12-10 21:22:42.493	6044	Retro Fisher Price Toys - Record Player	1	1
2010-12-11 14:35:42.590	6045	Retro Fisher Price Toys - Record Player	2	2
2010-12-12 21:31:26.003	6047	Vineyard Tour Wine Tasting & Lunch for Two	1	1
2010-12-12 22:33:23.670	6048	Angel Chimes	1	1
2011-01-04 10:48:46.000	6050	Football Ground Street Sign	1	1
2010-12-14 20:39:41.597	6051	Retro Weather Fridge Magnets	1	1
2010-12-14 22:33:09.113	6052	Terracotta Piggy Bank	1	1
2010-12-14 23:37:27.000	6053	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2010-12-15 12:25:25.763	6054	Snakes & Ladders and Ludo Retro Board Game	1	1
2010-12-16 12:49:29.057	6058	Flying Hats Retro Board Game	1	1
2010-12-18 13:01:08.460	6062	Retro Fisher Price Toys - Record Player	1	1
2010-12-18 14:59:50.023	6063	The Most Amazing Places on Britain's Coast  Book	1	1
2010-12-19 00:21:25.870	6065	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2010-12-20 22:46:54.660	6070	Retro Fisher Price Toys - Record Player	1	1
2010-12-21 16:54:26.257	6073	Retro Fisher Price Toys - Record Player	1	1
2010-12-22 21:07:34.747	6074	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2010-12-22 22:40:46.447	6075	Film Quote Babygrow - Nobody Puts Baby in the Corner	2	2
2011-01-04 10:38:43.000	6077	Retro Weather Fridge Magnets	2	2
2010-12-24 14:51:42.897	6079	Terracotta Piggy Bank	1	1
2010-12-26 08:17:31.020	6084	Grow It	1	1
2011-01-02 12:22:09.993	6108	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-01-08 19:04:27.637	6119	Silver Loveheart Necklace	1	1
2011-01-12 11:22:06.270	6121	Blow Football Retro Board Game	1	1
2011-01-16 16:55:30.513	6124	Retro Fisher Price Toys - Two Tune Television	1	1
2011-01-16 16:55:30.513	6124	Retro Fisher Price Toys - Teaching Clock	1	1
2011-01-17 16:15:12.000	6125	Haynes  The Dad's Manual  Book	1	1
2011-01-17 16:15:12.000	6125	Paper Yachts	1	1
2011-01-17 16:15:12.000	6125	Personalised Ladybird Fairy Tales	1	1
2011-01-20 15:59:08.000	6127	Wooden Acrobatic Monkey	1	1
2011-01-28 09:22:08.313	6130	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-01 09:25:28.463	6132	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-06 16:41:14.133	6138	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-08 22:43:16.520	6140	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-08 22:43:16.520	6140	Film Quote Babygrow - The Force is Strong in This One	1	1
2011-02-08 22:43:16.520	6140	Film Quote Babygrow - The Force is Strong in This One	1	1
2011-02-09 16:42:27.230	6141	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-09 16:42:27.230	6141	Film Quote Babygrow - The Force is Strong in This One	1	1
2011-02-12 19:29:39.470	6143	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-17 17:43:46.370	6144	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-17 17:43:46.370	6144	Film Quote Babygrow - The Force is Strong in This One	1	1
2011-02-18 17:20:18.953	6145	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-02-19 09:48:54.247	6147	Film Quote Babygrow - The Force is Strong in This One	1	1
2011-02-25 22:28:57.773	6149	The Stamp Collection Bone China Mugs	1	1
2011-02-28 22:15:55.627	6150	Film Quote Babygrow - Nobody Puts Baby in the Corner	2	2
2011-03-01 00:13:05.460	6151	Film Quote Babygrow - Nobody Puts Baby in the Corner	1	1
2011-03-08 10:20:46.007	6154	Film Quote Babygrow - The Force is Strong in This One	2	2
2011-03-08 10:20:46.007	6154	Balloon Powered Train	1	1

Open in new window

0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question