help with a query syntax and grouping

A

Ashley

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 accpted by
supplier,
total no of orders declined

ORDERS_SHIPPED


OrderID int, [Shipped Date] datetime


1 '1/2/05'
2 '2/3/05'
6 5/6/06'


ORDERS (1st 2 columns)


supplierID int OrderID int
111 1
111 2
111 8
222 3
222 4
333 5
333 6


ORDERS (3rd and 4th cloumn)


status char, amount int
'accepted' 1000
'accepted' 2500
'declined' 200
'accepted' 2000
'accepted' 3000
'declined' 2000
'accepted' 500


SUPPLIER


supplierid supplier
111 ABC
222 ROCKWAY
333 CASTLE


THIS IS THE EXPECTED RESULT


supplier OrdersShipped OrdersPending OrdersAccepted
ABC 2 1 2



ROCKWAY 0 2 1


CASTLE 1 0 1


NEED TO ADD ANOTHER COLUMNs WITH


OrdersDeclined AmtShipped
1 3500
0 0
1 500


AND


AMTDECLINED AMTPENDINGSHIPPING
200 0
0 5000
2000 0


PENDING IS WHICH IS ACCEPTED BUT NOT SHIPPED


I am trying something like this. I AM NOT AN ACCESS PROGRAMMER AND
WOULD
APPRECIATE HELP WITH THIS. I AM DOING SOMETHING WRONG HERE.


SELECT s.supplier,


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSSHIPPED]


sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSPENDING],


sum (IIf((o.Status ="ACCEPTED") ) ,1,0)) AS [ORDERSACCEPTED],


sum (IIf((o.Status ="DECLINED") ) ,1,0)) AS [ORDERSDECLINED],


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,o.amount ,0)) AS [AMOUNTSHIPPED]


sum (IIf((o.Status ="DECLINED") ) ,o.orderamount,0)) AS [AMTDECLINED],



sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,o.amount,0)) AS [AMTPENDINGSHIPPING],


FROM


supplier as s,
orders o,
ORDERS_SHIPPED os


WHERE


o.orderid = os.orderid
and o.supplierid = s.supplierid


GROUP BY


o.supplierid
 
G

Guest

To conditionally count rows you sum an expression which evaluates to 0 or 1.
Summing all the ones is the same as counting the rows which cause the
expression to evaluate to 1.

To count the orders shipped and pending you can use subqueries.

Similarly you can get the amount shipped and amount pending shipping with
subqueries.

To sum the amount declined sum the amount multiplied by the return value of
an expression which returns 1 or 0.

So putting it all together should require something like this:

SELECT Supplier,
(SELECT COUNT(*)
FROM Orders AS O1
WHERE O1.SupplierID = Suppliers.SupplierID
AND EXISTS
(SELECT *
FROM Orders_Shipped
WHERE Orders_Shipped.OrderID = O1.OrderID)) As OrdersShipped,
(SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.SupplierID = Suppliers.SupplierID
AND NOT EXISTS
(SELECT *
FROM Orders_Shipped
WHERE Orders_Shipped.OrderID = O2.OrderID)) As OrdersPending,
SUM(IIF(Status = "Accepted",1,0)) As OrdersAccepted,
SUM(IIF(Status = "Declined",1,0)) As OrdersDeclined,
(SELECT SUM(Amount)
FROM Orders AS O3
WHERE O3.SupplierID = Suppliers.OrderID
AND EXISTS
(SELECT *
FROM Orders_Shipped
WHERE Orders_Shipped.OrderID = O3.OrderID)) As AmtShipped,
SUM(Amount * IIF(Status = "Declined",1,0)) AS AmtDeclined,
(SELECT SUM(Amount)
FROM Orders AS O4
WHERE Status = "Accepted"
AND NOT EXISTS
(SELECT *
FROM Orders_Shipped
WHERE Orders_Shipped.OrderID = O4.OrderID)) As AmtPendingShipping
FROM Suppliers INNER JOIN Orders
ON Orders.SupplierID = Suppliers.SupplierID
GROUP BY Suppliers.SupplierID, Supplier;

You'll appreciate that I haven't been able to test this so it might require
some debugging.

Ken Sheridan
Stafford, England

Ashley said:
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 accepted by
supplier,
total no of orders declined

ORDERS_SHIPPED


OrderID int, [Shipped Date] datetime


1 '1/2/05'
2 '2/3/05'
6 5/6/06'


ORDERS (1st 2 columns)


supplierID int OrderID int
111 1
111 2
111 8
222 3
222 4
333 5
333 6


ORDERS (3rd and 4th cloumn)


status char, amount int
'accepted' 1000
'accepted' 2500
'declined' 200
'accepted' 2000
'accepted' 3000
'declined' 2000
'accepted' 500


SUPPLIER


supplierid supplier
111 ABC
222 ROCKWAY
333 CASTLE


THIS IS THE EXPECTED RESULT


supplier OrdersShipped OrdersPending OrdersAccepted
ABC 2 1 2



ROCKWAY 0 2 1


CASTLE 1 0 1


NEED TO ADD ANOTHER COLUMNs WITH


OrdersDeclined AmtShipped
1 3500
0 0
1 500


AND


AMTDECLINED AMTPENDINGSHIPPING
200 0
0 5000
2000 0


PENDING IS WHICH IS ACCEPTED BUT NOT SHIPPED


I am trying something like this. I AM NOT AN ACCESS PROGRAMMER AND
WOULD
APPRECIATE HELP WITH THIS. I AM DOING SOMETHING WRONG HERE.


SELECT s.supplier,


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSSHIPPED]


sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSPENDING],


sum (IIf((o.Status ="ACCEPTED") ) ,1,0)) AS [ORDERSACCEPTED],


sum (IIf((o.Status ="DECLINED") ) ,1,0)) AS [ORDERSDECLINED],


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,o.amount ,0)) AS [AMOUNTSHIPPED]


sum (IIf((o.Status ="DECLINED") ) ,o.orderamount,0)) AS [AMTDECLINED],



sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,o.amount,0)) AS [AMTPENDINGSHIPPING],


FROM


supplier as s,
orders o,
ORDERS_SHIPPED os


WHERE


o.orderid = os.orderid
and o.supplierid = s.supplierid


GROUP BY


o.supplierid
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top