Solved

SQL syntax

Posted on 2011-03-11
3
269 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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