Adding count to query without duplicating original select query

Question

Post reply

Adding count to query without duplicating original select query

ue_cakar

Grasshopper

Points: 16
More actions
August 5, 2014 at 8:16 pm

#309727

Hello ladies and gentlemen,
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,
_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,
WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
ORDER BY StkItem.ItemGroup, StkItem.Code, _bvSerialNumbersFull.SNTxDate
What this does is basically get's an inventory of certain items and does an aging analysis on them. However, the way the system works connected to this database there are some issues that need to be sorted out. This solution requires that a count on the serial number be made on the resultant dataset of this query and that count column be added in for the respective serial records.
Now the count bit is easy, and one solution I wrote is:
SELECT _bvSerialMasterFull.SerialNumber, COUNT(_bvSerialMasterFull.SerialNumber) AS SerialCount
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
GROUP BY _bvSerialMasterFull.SerialNumber
Thus, using an inner join with the original query we can have:
SELECT Cnt.SerialCount, _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,
_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,
WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
INNER JOIN (
SELECT _bvSerialMasterFull.SerialNumber, COUNT(_bvSerialMasterFull.SerialNumber) AS SerialCount
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
GROUP BY _bvSerialMasterFull.SerialNumber
) Cnt ON Cnt.SerialNumber=_bvSerialMasterFull.SerialNumber
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
However, as you can see, the original select query is run twice and joined together.
What I was hoping for is this to be done in the original query without the need to duplicate the original query. Can this be done? If so, your ideas, inputs and code is much appreciated.
Thanks in advance.

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

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

CodeMuddler SSC Eights! Points: 925 More actions · Answer 1

I think this may do what you want?

SELECTCOUNT(*) OVER (PARTITION BY _bvSerialMasterFull.SerialNumber) AS SerialCount,

_bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,

_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,

_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,

_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,

_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,

WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup

FROM _btblInvoiceLineSN INNER JOIN

_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN

_bvSerialMasterFull INNER JOIN

_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN

WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON

_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND

_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN

StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink

WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',

'070', '080', '100', '150', '300', '400'))

ORDER BY StkItem.ItemGroup, StkItem.Code, _bvSerialNumbersFull.SNTxDate

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

ue_cakar Grasshopper Points: 16 More actions · Answer 2

Brilliant!

Does exactly as I was hoping for. Great, Thank you very much for your response. Now I guess one needs to reap up on Partition BY to understand how it works.