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

SQL syntax

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
newbie27
Asked:
newbie27
  • 2
1 Solution
 
jonaskaCommented:
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
 
newbie27Author Commented:
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
 
newbie27Author Commented:
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
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
Tackle projects and never again get stuck behind a technical roadblock.
Join Now