November 20, 2013 at 12:54 am
Hi Guys
I need help. Is there a way to make the following into one query. What its doing is taking a table and grouping the rows according. What I need is a way to count the duplicatekey column after the group by has been done.
SELECT OriginalVendorNumber, InvoiceNumber, InvoiceDate, AbsoluteAmount, DuplicateKey
into CR2
FROM dbo.Criteria_02
GROUP BY OriginalVendorNumber, InvoiceNumber, InvoiceDate, AbsoluteAmount, DuplicateKey
GO
Select duplicatekey,count(duplicatekey) as Count
into cr2_Count
from cr2
group by duplicatekey
GO
SELECT CR2.OriginalVendorNumber, CR2.InvoiceNumber, CR2.InvoiceDate, CR2.AbsoluteAmount, CR2.DuplicateKey, cr2_Count.Count
into CR2_With_Count
FROM CR2 INNER JOIN
cr2_Count ON CR2.DuplicateKey = cr2_Count.duplicatekey
November 20, 2013 at 1:04 am
Is it possible to post some sample data and desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 20, 2013 at 1:50 am
I think this is it.
SELECT c.OriginalVendorNumber
, c.InvoiceNumber
, c.InvoiceDate
, c.AbsoluteAmount
, c.DuplicateKey
, capp.CountOf
FROM dbo.Criteria_02 c
CROSS APPLY ( SELECT duplicatekey
, COUNT(duplicatekey) AS CountOf
FROM dbo.Criteria_02 ca
WHERE ca.duplicatekey = c.DuplicateKey
GROUP BY duplicatekey
) capp
GROUP BY OriginalVendorNumber
, InvoiceNumber
, InvoiceDate
, AbsoluteAmount
, DuplicateKey
GO
November 20, 2013 at 2:06 am
Data with Groupby (Result)
OriginalVendorNumberInvoiceNumberInvoiceDateAbsoluteAmount DuplicateKey
0000002398 27468 2011-01-20 13212.90 SD_02_AN04_001155
0000062582 27468 2011-01-20 13212.90 SD_02_AN04_001155
Need the count of how many time the duplicatekey is present:
OriginalVendorNumberInvoiceNumberInvoiceDateAbsoluteAmount DuplicateKey Count
0000002398 27468 2011-01-20 13212.90 SD_02_AN04_0011552
0000062582 27468 2011-01-20 13212.90 SD_02_AN04_0011552
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply