Post #24,202
1/13/02 10:27:42 PM
|
How to build this SQL statement?
So, I have two tables: PRODUCTS, and ORDER_DETAILS. On the ORDER_DETAILS, I have the columns "OrderID", "ProductID", "Quantity", "Unit_Price", and "Discount" (as a percent value).
What I want:
For each Product, I need the sum of Quantity, and the sum of (FOR EACH OrderID WHERE ORDER_DETAILS={Current Product} Quantity*Unit_Price*(1-Discount)), and then I need to order by the value that that little function returns.
Now, I know enough to do:
SELECT Products.ProductName, SUM(Order_Details.Quantity), (NEEDHELP)
FROM Products, Order_Details
GROUP BY Products.ProductName
ORDER BY (NEEDHELP)
But, where I have (NEEDHELP) in there, I'm wondering how to do it. The main problem I'm wondering about is if I do something like SUM(Order_Details.Quantity*Order_Details.Unit_Price*(1-Order_Details.Discount)) would work...
Thanks for the help!
"He who fights with monsters might take care lest he thereby become a monster. And if you gaze for long into an abyss, the abyss gazes also into you." - Friedrich Nietzsche
|
Post #24,266
1/14/02 10:31:51 AM
|
Possible answer, not tested
SELECT
Products.ProductName,
SUM(Order_Details.Quantity),
SUM ( Order_Details.Quantity * Order_Details.Unit_Price * (1-Order_Details.Discount) ) AS TotalCost
FROM
Products,
Order_Details
GROUP BY
Products.ProductName
ORDER BY
TotalCost
I *think* the GROUP BY clause will automagically cause the various sums to be aggregated. Otherwise, I think you'll need a subselect. We have to fight the terrorists as if there were no rules and preserve our open society as if there were no terrorists. -- [link|http://www.nytimes.com/2001/04/05/opinion/BIO-FRIEDMAN.html|Thomas Friedman]
|
Post #24,302
1/14/02 1:36:29 PM
|
That's the real problem...
...I don't have an SQL database handy to test it in. :(
"He who fights with monsters might take care lest he thereby become a monster. And if you gaze for long into an abyss, the abyss gazes also into you." - Friedrich Nietzsche
|
Post #24,307
1/14/02 1:46:48 PM
|
Uh yeah?
Just get one of those freebie open sourced databases.
Like:
[link|http://www.mysql.org|MySQL]
[link|http://www.postrgessql.org|Postgres]
Or sign up for Oracle's Developer account for free and download their 9i Database for free. They have NT/2000/XP, Linux, Solaris, etc versions (sorry no OS/2).
"Will code Visual BASIC for cash."
|
Post #24,322
1/14/02 3:08:35 PM
|
I'll try to try it when I get home
And BTW where are all the database experts hiding today?
We have to fight the terrorists as if there were no rules and preserve our open society as if there were no terrorists. -- [link|http://www.nytimes.com/2001/04/05/opinion/BIO-FRIEDMAN.html|Thomas Friedman]
|
Post #24,333
1/14/02 4:19:37 PM
|
Looks right to me
If it doesn't let you order on the resulting TotalCost field then try nesting like this:
select *
from (SELECT Products.ProductName,
SUM(Order_Details.Quantity),
SUM(Order_Details.Quantity * Order_Details.Unit_Price * (1-Order_Details.Discount)) AS TotalCost
FROM Products,
Order_Details
GROUP BY Products.ProductName)
order by TotalCost
Darrell Spice, Jr.
[link|http://home.houston.rr.com/spiceware/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #24,352
1/14/02 5:39:40 PM
|
Oughta work in Oracle, anyway, AFAIK.
|
Post #24,338
1/14/02 4:28:50 PM
|
FWIW, I know you can't do that in Access
It won't allow ordering by an aggregate value. You'd have to either use a stored query, or build a new intermediate table. Other DB's, I am sure, are more powerful and will do what drew et al say they will.
---------------------------------
A stupid despot may constrain his slaves with iron chains; but a true politician binds them even more strongly by the chain of their own ideas;...despair and time eat away the bonds of iron and steel, but they are powerless against the habitual union of ideas, they can only tighten it still more; and on the soft fibres of the brain is founded the unshakable base of the soundest of Empires."
Jacques Servan, 1767
|
Post #24,342
1/14/02 4:37:45 PM
|
Uh, how about an Access Query based on an Access query?
Do one query that generates the sums, etc. Then a second query based on the first that groups them. Access queries can be based on another Access query. Quite a useful thing to do in Access.
"Will code Visual BASIC for cash."
|
Post #24,615
1/16/02 11:54:30 AM
|
That's kinda what I meant by intermediate table
whether that's recalced on the fly with a second query or INSERT INTO doesn't matter much to me
---------------------------------
A stupid despot may constrain his slaves with iron chains; but a true politician binds them even more strongly by the chain of their own ideas;...despair and time eat away the bonds of iron and steel, but they are powerless against the habitual union of ideas, they can only tighten it still more; and on the soft fibres of the brain is founded the unshakable base of the soundest of Empires."
Jacques Servan, 1767
|
Post #24,372
1/14/02 7:36:48 PM
|
Here's one way
tested on SQL 7.0
select products.productname, isnull(sums.totalquantity, 0), isnull(sums.extendedprice, 0)
from products left outer join
(
select productid, sum(quantity) as TotalQuantity,
sum(quantity * unit_price*(1-discount)) as ExtendedPrice
from order_details
group by productid
) sums
on products.productid=sums.productid
order by extendedprice desc
If you don't care about products that haven't sold, you can drop the isnulls and do an inner join.
FAQ! We're scrod!
|
Post #24,521
1/15/02 7:23:17 PM
|
Subqueries
I find the group by stuff to be a bit cumbersome and subqueries to be a bit easier to work with. Just playing but here's what I cam up with for SQLServer: CREATE TABLE products(
productid int,
productname VARCHAR(50))
GO
INSERT INTO products VALUES(1, 'Widgets')
INSERT INTO products VALUES(2, 'Gadgets')
GO
CREATE TABLE order_details(
orderid int,
productid int,
quantity int,
unit_price money,
discount money)
GO
INSERT INTO order_details VALUES(1, 1, 10, 2.50, 0.05)
INSERT INTO order_details VALUES(2, 1, 5, 2.50, 0.08)
GO
SELECT
productname,
quantity = ISNULL((
SELECT SUM(quantity)
FROM order_details
WHERE productid = tab.productid), 0),
totalcost = ISNULL((
SELECT SUM(ROUND(quantity * unit_price * (1-discount), 2))
FROM order_details
WHERE productid = tab.productid), 0)
FROM products tab
ORDER BY totalcost
|
