newbie27
asked on
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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am geting results like this, do you need we can group the products by name?
Thanks
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
ASKER
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.