to find sum of orders accepted and not yet shipped

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

to find sum of orders accepted and not yet shipped

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-28 : 17:57:18

I have 2 tables

Orders

OrderID, Shipped Date
1, 1/2/05

and another table

OrderID, status
1 , accepted
2, accepted
3, pending
4, accepted

now I want to find out the sum of orders where status is accepted and they have not been shipped this means there is no record of them in the first table.

Ashley Rhodes

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-28 : 18:07:22

You said the sum or the orders but didn't specify that one of the tables has an amount column, so I assumed that it is in the second table. I'm not sure why you have the status and the shipping date for an order in two different tables, so you may have a design problem, but all that aside, this should give you what you want:

CREATE TABLE #Orders 
(OrderID int, [Shipped Date] datetime)
INSERT #Orders
SELECT 1, '1/2/05'

CREATE TABLE #OrderStatus
(OrderID int, status varchar(10), amount decimal(9,2))
INSERT #OrderStatus
SELECT 1 , 'accepted', 1000.00 UNION ALL
SELECT 2, 'accepted', 2500.79 UNION ALL
SELECT 3, 'pending', 1999.45 UNION ALL
SELECT 4, 'accepted', 3000.00

SELECT sum(amount)
FROM #OrderStatus
WHERE status = 'accepted' 
AND NOT EXISTS (SELECT * FROM #Orders WHERE OrderID = #OrderStatus.OrderId)

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-29 : 09:25:12

What is I want to see sum of orders which have been shipped as well
I would need a seperate column for that.

Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 09:31:12

Do you have some sample data for the two tables?

Peter Larsson
Helsingborg, Sweden

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-29 : 10:21:09

THIS IS THE SAMPLE DATE
I want to see supplierID, sum of amount shipped and sum of amount not shipped
and group them by suppier, total no of orders accpeted by the vendor, total no of orders declined

CREATE TABLE #OrdersShipped
(OrderID int, [Shipped Date] datetime)
INSERT #OrdersShipped
SELECT 1, '1/2/05' union all
SELECT 2, '2/3/05'

CREATE TABLE #OrderStatus
(supplierIDint, OrderID int, status varchar(10), amount decimal(9,2))
INSERT #OrderStatus
SELECT 111, 1 , 'accepted', 1000.00 UNION ALL
SELECT 111, 2, 'accepted', 2500.00 UNION ALL
SELECT 222, 3, 'pending', 2000 UNION ALL
SELECT 222, 4, 'accepted', 3000.00 UNION ALL
Select 333, 5, 'declined', 200 UNION ALL
Select 333, 6, 'accepted', 500

Ashley Rhodes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-29 : 14:09:18

You really need to learn SQL if you cannot do this simple query.

Learn SQL:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

CODO ERGO SUM

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-02 : 15:33:30

I know the SQL for this simple query very well.
I WANTED TO GROUP EVERTHING TOGETHER IN 1 QUERY.
LOOK AT THE RESULT OF THIS. HOW DO I GET the SUMS of each field
means GROUP by SUPPLIER ID in one query without using derived or temp tables

CREATE TABLE #OrdersShipped
(OrderID int, [Shipped Date] datetime)
INSERT #OrdersShipped
SELECT 1, '1/2/05' union all
SELECT 2, '2/3/05'

CREATE TABLE #Orders
(supplierID int, OrderID int, status varchar(10), amount int)
INSERT #Orders
SELECT 111, 1, 'accepted', 1000 UNION ALL
SELECT 111, 2, 'accepted', 2500 UNION ALL
SELECT 111, 8, 'declined', 200 UNION ALL
SELECT 222, 3, 'accepted', 2000 UNION ALL
SELECT 222, 4, 'accepted', 3000 UNION ALL
SELECT 333, 5, 'declined', 2000 UNION ALL
SELECT 333, 6, 'accepted', 500

select
supplierID,
sum(case when o.status = 'accepted' then 1 else 0 end) as [ORDERS ACCEPTED],
sum(case when o.status = 'declined' then 1 else 0 end) as [ORDERS DECLINED],
(case when o.status = 'accepted' and o.orderid in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then 1 else 0 end ) as [ORDERS SHIPPED] ,

(case when o.status = 'accepted' and o.orderid not in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then 1 else 0 end ) as [ORDERS PENDING SHIPPING] ,

sum(case when o.status = 'declined' then 1 else 0 end ) as [ORDERS DECLINED] ,

(case when o.status = 'accepted' and o.orderid in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then o.amount else 0 end ) as [AMOUNT OF ORDERS SHIPPED] ,

(case when o.status = 'accepted' and o.orderid not in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then o.amount else 0 end ) as [AMOUNT OF PENDING SHIPPING] ,

sum(case when o.status = 'declined' then o.amount else 0 end ) as [AMOUNT OF ORDERS DECLINED]

From #ORDERS O

GROUP BY o.supplierid, O.status, O.OrderID, o.amount
GO

drop table #OrdersShipped , #orders

Ashley Rhodes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 15:45:20

There are no derived tables in the query you posted. Does it produce the results you want?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-02 : 16:45:29

There are no aggregations for

1) [ORDERS SHIPPED]
2) [ORDERS PENDING SHIPPING]
3) [AMOUNT OF ORDERS SHIPPED]
4) [AMOUNT OF PENDING SHIPPING]

Also, there are too many groupings

1) o.supplierid
2) O.status
3) O.OrderID
4) o.amount

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-02 : 16:52:26

Play around with this query for a while and se if the result is what you expect.

SELECT		o.SupplierID,
		SUM(CASE WHEN o.Status = 'Accepted' THEN 1 ELSE 0 END) [ORDERS ACCEPTED],
		SUM(CASE WHEN o.Status = 'Declined' THEN 1 ELSE 0 END) [ORDERS DECLINED],
		SUM(CASE WHEN o.Status = 'Accepted' AND os.OrderID IS NOT NULL THEN 1 ELSE 0 END) [ORDERS SHIPPED],
		SUM(CASE WHEN o.Status = 'Accepted' AND os.OrderID IS NULL THEN 1 ELSE 0 END) [ORDERS PENDING SHIPPING],
		SUM(CASE WHEN o.Status = 'Declined' THEN 1 ELSE 0 END) [ORDERS DECLINED],
		SUM(CASE WHEN o.Status = 'Accepted' AND os.OrderID IS NOT NULL THEN o.Amount ELSE 0 END) [AMOUNT OF ORDERS SHIPPED],
		SUM(CASE WHEN O.Status = 'Accepted' AND os.OrderID IS NULL THEN o.Amount ELSE 0 END) [AMOUNT OF PENDING SHIPPING],
		SUM(CASE WHEN o.Status = 'Declined' THEN o.Amount ELSE 0 END) [AMOUNT OF ORDERS DECLINED]
FROM		Orders o
LEFT JOIN	OrdersShipped os ON os.OrderID = o.OrderID 
GROUP BY	o.SupplierID

Peter Larsson
Helsingborg, Sweden

sa
Yak Posting Veteran

77 Posts

Posted - 2006-10-02 : 19:35:39

SELECT orderid,status,SUM(amount)--assuming)
FROM anotherTable
WHERE orderid NOT IN(SELECT orderid FROM orders)
GROUP BY orderid

This is how i understood your need. Wish it will work for you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources