Solved

SQL syntax

Posted on 2011-03-11
3
263 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
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
jonaska earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now