September 14, 2010 at 5:08 am
Until half an hour ago, I thought I knew what I was doing......
I've done this before, but totally forgotten how and can't find my original code. Please help!
I have two tables with exactly the same structure, except one has live data and the other has archive data. I want to pull out totals for each product plus a min value. For instance, this is the data for one item:
Live table data (as csv)
prod, ordno, qty,compdate
04121065-401, WO287482, 1, 09/12/2009
Archive table data (as csv)
prod, ordno, qty, compdate
04121065-401, WO130048, 1, 07/05/2004
04121065-401, WO191763, 1, 03/08/2006
04121065-401, WO139028, 3, 07/09/2004
04121065-401, WO197723, 1, 13/10/2006
What I want is for each prod, show the count of the wo column, the sum of the qty column and the min (e.g. oldest) date in the compdate column in a single line from two tables. So, for the above example data, I would get a single resultset of:
04121065-401, 5, 7, 07/05/2004
I thought this would work:
SELECTprod, SUM(ordcnt) AS [Order Count], SUM(totqty) AS [Total Qty],
MIN(lastdate) AS [First Sold]
FROM(
SELECTprod, COUNT(ordno) AS [ordcnt], SUM(qty) AS [totqty],
MIN(compdate) AS [lastdate]
FROM ordlive
GROUP BY prod
UNION
SELECTprod, COUNT(ordno), SUM(qty), MIN(compdate)
FROM ordarchive
GROUP BY prod
)
GROUP BY prod
ORDER BY prod
but, I get an incorrect syntax error on line 13 by the outer GROUP BY. I'm sure the last time I did one of these is was something like this, but my mind has gone blank.
September 14, 2010 at 5:24 am
SELECT prod, SUM(ordno) AS [Order Count], SUM(qty) AS [Total Qty],
MIN(compdate) AS [First Sold]
FROM (
SELECT prod, ordno, qty,
compdate
FROM ordlive
GROUP BY prod
UNION
SELECT prod,ordno, qty, compdate
FROM ordarchive
GROUP BY prod
) AS A
GROUP BY prod
ORDER BY prod
First try using without the bold section if it doesn't work try with bold section
September 14, 2010 at 5:43 am
Duh!!!!
It was the table name alias (the bit in the bold) I was missing!!!!!
Thank you sanmatrix for jogging my memory!
September 14, 2010 at 5:46 am
😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply