| Simple SQL Question [message #1336] |
Mon, 22 April 2002 11:04  |
I'm a Loser
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
Statement:
SELECT A.PRODUCT FROM PRODUCT_TABLE A
WHERE A.PRODUCT_PRICE > (SELECT SUM(B.ITEM_PRICE)
FROM PROD_ITEM_TABLE B
WHERE A.PRODUCT = B.PRODUCT
AND A.COMPANY = B.COMPANY)
A Product is comprised of multiple items...I want to see where the Product price is greater then the sum of it's item prices. How do I see the value of
SUM(B.ITEM_PRICE) by leaving the subselect where it is?
|
|
|
|
| Re: Simple SQL Question [message #1339 is a reply to message #1336] |
Tue, 23 April 2002 00:59  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, if you insist on leaving the subselect where it is you'd have to do something ugly like:
SELECT A.PRODUCT, SUM(C.ITEM_PRICE)
FROM PRODUCT_TABLE A , PROD_ITEM_TABLE C
WHERE A.PRODUCT_PRICE > (SELECT SUM(B.ITEM_PRICE)
FROM PROD_ITEM_TABLE B
WHERE A.PRODUCT = B.PRODUCT
AND A.COMPANY = B.COMPANY)
AND A.PRODUCT = C.PRODUCT
AND A.COMPANY = C.COMPANY
GROUP BY A.PRODUCT
Or, you could get rid of the subquery entirely and have:
SELECT a.PRODUCT,a.PRODUCT_PRICE,sum(b.ITEM_PRICE)
FROM product_table a, prod_item_table b
WHERE a.company = b.company
AND a.product = b.product
HAVING a.product_price > sum(b.item_price)
GROUP BY a.product,a.product_price;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
