Group By Query

Question

Group By Query

Richard O'Sullivan

Ten Centuries

Points: 1118
More actions
August 7, 2005 at 9:44 am

#88422

Hi All,
I have two tables that I need to combine to show a single record per Stockcode with the netted values from both tables. The code here is showing 2 records when item exists in both Tables.
l_sqlstmt = "SELECT StockCode, SUM(QtyOrder), (SUM(QtyOrder) * 0), SUM(QtyOrder) FROM tbl_LLDT WHERE LLNum = '" & p_llNo & "' GROUP BY StockCode UNION SELECT StockCode, (SUM(QtyScanned) * 0), SUM(QtyScanned), (SUM(QtyScanned) * -1) FROM tbl_DeviceLLDT WHERE LLNum = '" & p_llNo & "' GROUP BY StockCode"
TIA
Richard

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

Wrap the whole SELECT/UNION/SELECT in parenthesis, give it an alias like a table, and us it in the FROM clause of yet another SELECT/GROUP BY. By the way, SUM(QtyScanned)*0 could be repalced with just a zero...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Richard - are you looking for something like this:

SELECT B.StockCode, MAX(B.QtyOrder) AS QtyOrderd, MAX(B.QtyScanned) AS QtyScanned, (MAX(B.QtyOrder) - MAX(B.QtyScanned))AS QtyRemaining

FROM

(SELECT StockCode, SUM(QtyOrder)AS QtyOrder, 0 AS QtyScanned FROM #tbl_LLDT WHERE LLNum = 'p_llNo' GROUP BY StockCode

UNION

SELECT StockCode, 0 AS QtyOrder, SUM(QtyScanned)AS QtyScanned

FROM #tbl_DeviceLLDT WHERE LLNum = 'p_llNo' GROUP BY StockCode)B

GROUP BY B.StockCode

**ASCII stupid question, get a stupid ANSI !!!**

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 3

Thanks Jeff and Sushila for your help. It has pointed me in the direction I need. I was able to get the skeleton of what I need from your pointers.

Many Thanks

Richard