Nested SELECT AND GROUP BY Query
576482May 8 2007 — edited May 8 2007I have the following query,
SELECT SUM(ROUND(to_char((ENDTIME - STARTTIME) * 24 * 60 * 60), 0)) AS LostTime, GOODQUANTITY
FROM TABLE1
WHERE (NAME = 'widget') AND (TRANSACTIONCODE = '25') AND (ENDTIME IS NOT NULL) AND
(CREATEDON >= TO_DATE('5 - 8 - 2007 12 : 00 : 00 PM ', 'MM - DD - YYYY HH12 : MI : SSPM')) OR
(GOODQUANTITY = ANY
(SELECT TOTALGOODQUANTITY
FROM TABLE1 TABLE1_1
WHERE (NAME = 'widget') AND (TRANSACTIONCODE = '50') AND (ENDTIME IS NULL)))
GROUP BY NAME, GOODQUANTITY
It returns two rows when I would like for it to only return one.
I have tried grouping by NAME only, with no luck.
Transactioncode 25 will have multiple records, where as transactioncode 50 will only have 1 record at all times. Would like to get both results on the same line.
I would also like to add this as another sub query to the same statement from another table.
SELECT SUM(QUANTITY) AS Waste
FROM TABLE_WASTE
WHERE (NAME = 'widget') AND (CREATEDON >= TO_DATE('05/08/2007 12:00:00PM', 'MM - DD - YYYY HH12:MI:SSPM'))
GROUP BY NAME
The end result I would like to have only three records returned on a single row. I have been fighting this for a week now. Not too much hair left to pull out of my head...