Sub-Totalling - Help.

[milton747]

Hi everyone,

I am going crazy trying to nest subqueries to get simple sub-totals. The goal is a row like this:
STATE | CUSTID | CUSTNAME | ORDERID | STATE TOTAL | CUSTOMER TOTAL | ORDER TOTAL

I'm trying to wrap INNER JOINS around my core SQL (which returns ORDER TOTAL) to get the other 2 totals, for CUSTOMER and STATE. Simple tables shown below. But I can't seem to stack the inner joins correctly. Losing my hair.

Any help welcome.
Thanks. Milt.

-------------------------------

== customer table ==
- state
- custID
- custname
== order table ==
- orderID
- custID
== items ==
- orderID
- price
- quantity

- Below works to return order total.

SELECT orders.custID, orders.orderID, SSA.OT
FROM orders
INNER JOIN
(SELECT
items.orderID,
SUM ( items.quantity * items.pricequote ) AS OT
FROM items
GROUP BY items.orderID ) SSA
ON SSA.orderID = orders.orderID

 

[drtree]

Hi Milt,

While not very elegant, I think this should work...

SELECT cust.[State], cust.CustID, cust.CustName, o.OrderID, o.OrderTotal, oc.CustomerTotal, s.StateTotal
FROM Customer cust 
  LEFT OUTER JOIN (SELECT o.CustID, o.OrderId, SUM(i.Quantity * i.Price) As OrderTotal
           FROM order o
             INNER JOIN items i
                 ON o.OrderId = i.OrderId
                   GROUP BY o.CustID, o.OrderId) o
  ON cust.CustID = o.CustID

  LEFT OUTER JOIN (SELECT o.CustID, SUM(i.Quantity * i.Price) As CustomerTotal
           FROM order o
             INNER JOIN items i
                 ON o.OrderId = i.OrderId
                   GROUP BY o.CustID) oc
  ON cust.CustID = oc.CustID

  LEFT OUTER JOIN (SELECT s.State, SUM(i.Quantity * i.Price) As StateTotal
           FROM order o
             INNER JOIN items i
                 ON o.OrderId = i.OrderId
                 INNER JOIN Customer c
                 ON o.CustID = c.CustID
                   GROUP BY s.[State]) s
  ON cust.[State] = s.[State]

I just used the same subquery 3 times and grouped it by the different attributes you wanted.

Also, you can change all those LEFT OUTER JOINs I used to INNER JOINs - I just included them in case you needed to see customers without sales (in addition to those who had made a purchaes).



HTH,

Doc Tree