Subquery Help

[spiderbaby]

Okay, I am trying to get this to work in subquery form because I seriously need to brush up on my subquery knowledge and for the life of me I cannot get it to work right. I can do innerjoins and get it to work but I know there is a way to do it other than innerjoins. I'm close but not close enough. Any help would be greatly appreciated.
SELECT (SELECT Cust_State
FROM Customers C
WHERE C.Cust_id = (SELECT Cust_id
FROM Orders O
WHERE O.Order_num = OT.Order_num)) AS State, Sales_total
FROM (SELECT Order_num, SUM(Quantity * Item_price) AS Sales_Total FROM Orderitems GROUP BY Order_num)OT

This gives me:
State Sales_Total
MI 1648
IN 329.6
AZ 1696
IL 189.6
MI 1867.5

My problem is that it's not summing up MI like it should. I've tried putting a group by in there and it doesn't work. Help please?

 

[NoCoolHandle]

OK, so you want a subtotal of orders by state..
If you just wanted the results I would probably do the folloing..

Select Cust_state,Sum( quantity * item_price)as OrderTotals
from Customers c join Orders O on c.Cust_id = O.cust_id
join OrderItems OI on OI.Order_Num = o.OrderNum
Group by Cust_State

 

[spiderbaby]

Here's the Innerjoin solution I have, which works like a charm, I'm just curious as to how to get the same thing using no innerjoins.
SELECT Customers.cust_state, SUM([quantity] * [item_price]) AS volume
FROM (Customers INNER JOIN
Orders ON Customers.cust_id = Orders.cust_id) INNER JOIN
OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_state;

 

[gmmastros]

Unless I'm missing something here, you don't need subqueries to get the data you are looking for. Does this return what you are looking for?

Select Cust_State,
       SUM(Quantity * Item_price) AS Sales_Total
From   Customers
       Left Join Orders On Customers.CustId = Orders.Cust_Id
       Left Join OrderItems On Orders.Order_Num = OrderItems.Order_Num
Group By Customers.Cust_State
Order By Customers.Cust_State

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[spiderbaby]

Thanks NoCoolHandle, I was just wondering if it was possible to do it without joins. I've got an upcoming program that I will probably have to use more subqueries than joins and argh, my subquery skills need some severe honing.

 

[spiderbaby]

gmmastros, you are indeed correct with my not needing subqueries to resolve this SQL but as a matter of personal interest I was trying ot figure it out. Someone at work was telling me that it wasn't possible but I think it is.

 

[gmmastros]

You could just cheat! Make your existing query be a subquery. Wrap another select around it with another sum

Select  Cust_State,
		Sum(Sales_Total) as Sales_Total
From    (
        SELECT (SELECT Cust_State
                FROM   Customers C
                WHERE C.Cust_id = (SELECT Cust_id
                                   FROM Orders O
                                   WHERE O.Order_num = OT.Order_num)) AS State, 
		        Sales_total
        FROM (
             SELECT Order_num, 
                    SUM(Quantity * Item_price) AS Sales_Total 
             FROM   Orderitems 
             GROUP BY Order_num)OT
        ) As SubTotal
Group By Cust_State

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NoCoolHandle]

If you need a subquery.. (and this one is a corelated subquery so it makes it a little ickier. - i.e. a subquery with a join to an outer query)

The problem that you are having is that the top level select isn't grouping on the state, it is a grouping on the order id, this is what is causing your query to go wacky.

You need to do something like

Select State, (select SUM(Quantity * Item_price) AS Sales_Total FROM Orderitems Oi where Order_num in (select Order Num from Orders where Custid in (Select Custid from customer where State = OT.State)))
From (Select distinct (state) from customer)


But ....

 

[NoCoolHandle]

Sorry forgot to alias the outer query..
Also you might want to count parens .. I have not data to check it against so it is very rough and probably missing a couple of small cross;s or dots on my t's and i's.




Select State, (select SUM(Quantity * Item_price) AS Sales_Total FROM Orderitems Oi where Order_num in (select Order Num from Orders where Custid in (Select Custid from customer where State = OT.State)))
From (Select distinct (state) from customer) Ot