Casey Crookston Casey Crookston - 11 days ago 5
SQL Question

Count number of rows that were returned in a join

This query successfully returns only the first row of a join that could potentially have more than one row.

WITH RevisionProducts AS (
SELECT
qr.LeadID,
p.Code,
ROW_NUMBER() OVER(PARTITION BY qr.LeadID ORDER BY qr.LeadID DESC) rownumber
FROM
QuoteRevisions qr
JOIN ...
)
SELECT
l.LeadID,
...
co.Name,
rp1.Code,
0 AS CodeCount
FROM
Leads l
JOIN Companies co on co.CompanyID = l.CompanyID
JOIN RevisionProducts rp1 ON rp1.LeadID = l.ID AND rp1.rownumber = 1


What I want to do now is replace...

0 AS CodeCount


...with the actual number of rows that would have been returned in the join, had we allowed them all. Can't figure out how to do this.

I'm not sure I need the CTE, but I figured it might be handy since I most likely need to reference the same query again for the count?

EDIT:

Ok it looks like I need to be more clear. So the query with in the CTE... let's run it with a WHERE clause:

SELECT
qr.LeadID,
p.Code,
ROW_NUMBER() OVER(PARTITION BY qr.LeadID ORDER BY qr.LeadID DESC) rownumber
FROM
QuoteRevisions qr
JOIN ...
WHERE
qr.LeadID = 151


And let's say that query returns 5 rows. So, in the first query, if we DID NOT limit the join to the first row only, then the join would have returned 5 rows when Lead.LeadID got to 151. So in the final dataset, there would have been 5 rows that were identical except for the rp1.Code column.

I have already limited the number of rows to 1, which is what I wanted. But now, I want to know how many rows would have been returned.

I hope that makes more sense.

DVT DVT
Answer

How about something like this?

WITH RevisionProducts AS (
    SELECT
        qr.LeadID,
        p.Code,
        ROW_NUMBER() OVER(PARTITION BY qr.LeadID ORDER BY qr.LeadID DESC) rownumber
        COUNT(*) OVER(PARTITION BY qr.LeadID ) rowcount
    FROM    
        QuoteRevisions qr
        JOIN ...
)
SELECT 
    l.ID,
    ...
    co.Name,
    rp1.Code,
    rp1.rowcount AS CodeCount
FROM 
    Leads l
    JOIN Companies co on co.CompanyID = l.CompanyID
    JOIN RevisionProducts rp1 ON rp1.LeadID = l.ID AND rp1.rownumber = 1
Comments