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
Solved

SQL syntax

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Date from a string 4 70
Query - which index being used? 2 60
Can Unique column have more than one Null? 8 54
Loops and updating in SQL Query 9 52
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 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