Combining SQL commands...

[milton747]

Hi Folks,


The SELECT statement at the bottom works good. It retrieves all the cols for every order. I need to improve it to allow the user to say,
"Only show orders where order total > $30000"
I'd guess I start out with something like:

SELECT items.orderID, ( SUM (items.pricequote * items.quantity) ) As OrderTotal others cols....
FROM all-tables
GROUP BY items.orderID and-other-columns
HAVING ( SUM (items.pricequote * items.quantity) ) > 30000

Then having retrieved the order-IDs that are >30000, how would I combine with big SQL below? Would a simple nested SQL do the trick? (ie: I have a list of order ID, and I want all the columns for each ID.)


PS: SQL PHILOSOPHY Q: Re: GROUP BY clause.
Any idea why you have to list ALL the non-aggregated columns in the GROUP BY? In this case, the order ID is a universal identifier, so I could simply say Group by Order ID, and that's enough. That would be enough instructions for a human to do the job, why not SQL? (Grouping by Item Description and seems silly, and a waste of CPU cycles.)


Thanks for any help.
Milt.
(Working all weekend on development...what a geek!)



== Good SQL ===

SELECT offices.state, offices.city, offices.address, offices.postalcode, offices.officeID,
salesreps.repID, salesreps.`last`, salesreps.`first`,
customers.creditrank, customers.repID, customers.address, customers.postalcode,
customers.city, customers.state, customers.custID, customers.customName, orders.orderID,
items.itemcode, items.description, items.pricequote, items.quantity,
orders.shipByDate
FROM customers, orders, items, offices, salesreps
WHERE customers.custID = orders.custID
AND orders.orderID = items.orderID
AND salesreps.repID = customers.repID
AND offices.officeID = salesreps.officeID
ORDER BY offices.officeID, salesreps.repID, customers.customName, orders.orderID

=====

 

[lespaul]

try this:

SELECT offices.state, offices.city, offices.address, offices.postalcode, offices.officeID,
salesreps.repID, salesreps.`last`, salesreps.`first`,
customers.creditrank, customers.repID, customers.address, customers.postalcode,
customers.city, customers.state, customers.custID, customers.customName, orders.orderID,
items.itemcode, items.description, items.pricequote, items.quantity,
orders.shipByDate
FROM customers INNER JOIN orders on Customers.CustID = Orders.CustID INNER JOIN items ON Orders.OrderID = Items.OrderID INNER JOIN salesrep on customer.repID = salesrep.repID INNER JOIN offices on SalesRep.OfficeID = Offices.OfficeID
WHERE Items.OrderID IN 
(SELECT items.orderID
FROM Items
HAVING (SUM(items.pricequote * items.quantity)) > 30000)

ORDER BY offices.officeID, salesreps.repID, customers.customName, orders.orderID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

One way:
SELECT F.state, F.city, F.address, F.postalcode, F.officeID,
R.repID, R.last, R.first,
C.creditrank, C.repID, C.address, C.postalcode,
C.city, C.state, C.custID, C.customName, rderID,
I.itemcode, I.description, I.pricequote, I.quantity,
O.shipByDate
FROM ((((customers AS C
INNER JOIN orders AS O ON C.custID = O.custID)
INNER JOIN items AS I ON rderID = I.orderID)
INNER JOIN salesreps AS R ON C.repID = R.repID)
INNER JOIN offices AS F ON R.officeID = F.officeID)
INNER JOIN (SELECT orderID FROM items GROUP BY orderID
HAVING Sum(pricequote * quantity) > 30000
) AS T ON rderID = T.orderID
ORDER BY F.officeID, R.repID, C.customName, rderID

Another way:
SELECT F.state, F.city, F.address, F.postalcode, F.officeID,
R.repID, R.last, R.first,
C.creditrank, C.repID, C.address, C.postalcode,
C.city, C.state, C.custID, C.customName, rderID,
I.itemcode, I.description, I.pricequote, I.quantity,
O.shipByDate
FROM ((((customers AS C
INNER JOIN orders AS O ON C.custID = O.custID)
INNER JOIN items AS I ON rderID = I.orderID)
INNER JOIN salesreps AS R ON C.repID = R.repID)
INNER JOIN offices AS F ON R.officeID = F.officeID)
INNER JOIN (SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items GROUP BY orderID
) AS T ON rderID = T.orderID
WHERE T.OrderTotal > 30000
ORDER BY F.officeID, R.repID, C.customName, rderID

Yet another way:
SELECT F.state, F.city, F.address, F.postalcode, F.officeID,
R.repID, R.last, R.first,
C.creditrank, C.repID, C.address, C.postalcode,
C.city, C.state, C.custID, C.customName, rderID,
I.itemcode, I.description, I.pricequote, I.quantity,
O.shipByDate
FROM (((customers AS C
INNER JOIN orders AS O ON C.custID = O.custID)
INNER JOIN items AS I ON rderID = I.orderID)
INNER JOIN salesreps AS R ON C.repID = R.repID)
INNER JOIN offices AS F ON R.officeID = F.officeID
WHERE (SELECT Sum(pricequote * quantity) FROM items WHERE orderID = rderID) > 30000
ORDER BY F.officeID, R.repID, C.customName, rderID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[milton747]

Mon Jun 6.

Hi PH,

Thanks so much so the reply.
All three SQL statements worked great.

I plan to use the 2nd which uses a WHERE vs. a HAVING.
The report tool I'm using (Actuate) is better at attaching params to a WHERE clause than a HAVING clause.

Big Thanks Again.
Milton.

 

[milton747]

PHV provided great SQL solutions. I tried altering it 'slightly' (big mistake) to use the WHERE format instead of INNER JOIN. SQL now gets "Too Few Parameters" errors.
Would appreciate fresh eyes the SQL.
The error is in the Sub-Query line I suspect. (Take it out and all works fine)

=== GOOD SQL ===========

SELECT F.state, F.city, F.address, F.postalcode, F.officeID,
R.repID, R.last, R.first,
C.creditrank, C.repID, C.address, C.postalcode,
C.city, C.state, C.custID, C.customName, rderID,
I.itemcode, I.description, I.pricequote, I.quantity,
O.shipByDate
FROM ((((customers AS C
INNER JOIN orders AS O ON C.custID = O.custID)
INNER JOIN items AS I ON rderID = I.orderID)
INNER JOIN salesreps AS R ON C.repID = R.repID)
INNER JOIN offices AS F ON R.officeID = F.officeID)
INNER JOIN (SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items GROUP BY orderID
) AS T ON rderID = T.orderID
WHERE T.OrderTotal > 30000
ORDER BY F.officeID, R.repID, C.customName, rderID

==== SQL gets Too Few Parameters error ===

SELECT offices.state, offices.city, offices.address, offices.postalcode, offices.officeID,
salesreps.repID, salesreps.last, salesreps.first,
customers.creditrank, customers.repID, customers.address, customers.postalcode,
customers.city, customers.state, customers.custID, customers.customName, orders.orderID,
items.itemcode, items.description, items.pricequote, items.quantity, orders.shipByDate
FROM offices, salesreps, customers, items, orders
WHERE offices.officeID = salesreps.officeID
AND salesreps.repID = customers.repID
AND customers.custID = orders.custID
AND orders.orderID = items.orderID
AND EXISTS
(SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items
WHERE orders.orderID = items.orderID AND OrderTotal > 3000000
GROUP BY orderID
)
ORDER BY offices.officeID, salesreps.repID, customers.customName, orders.orderID


================================


Thanks
Milton.

 

[lespaul]

why would you want to change the INNER JOINS to the WHERE clause and use the cartesian product? I'm pretty sure the JOINS are more efficient.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[jebry]

Hi Milton!

Instead of:

AND EXISTS
(SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items
WHERE orders.orderID = items.orderID AND OrderTotal > 3000000
GROUP BY orderID
)

Try:

AND orders.orderID IN (SELECT orderID
FROM items
WHERE orders.orderID = items.orderID AND Sum(pricequote * quantity)> 3000000
GROUP BY orderID
)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Golom]

The problem is this

(SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items
WHERE orders.orderID = items.orderID AND OrderTotal > 3000000
GROUP BY orderID
)

You cannot reference a column alias in the WHERE clause because the alias is not assigned until the SELECT clause is processed and it is the last clause to be processed. You need something like

(SELECT orderID, Sum(pricequote * quantity) As OrderTotal
FROM items
WHERE orders.orderID = items.orderID  
GROUP BY orderID
[COLOR=red]HAVING Sum(pricequote * quantity) > 300000[/color]
)

I do agree with Leslie however. The INNER JOINS are the preferred way of doing this.