Aggregate function related problem in SQL Server

Question

When ever I am using the below query :

SELECT  
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    (SELECT COUNT (*) FROM tbUS_ReferJob WHERE iJobId_FK = 202424 AND sReferredFrom = 'F' AND iUserId_FK = 9550) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

It is giving the correct result.

JobID   RowNumber   FaceBook_Applications
202424  1           2

But when I use the query below

SELECT
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    SUM(CASE WHEN RJ.sReferredFrom = 'F' THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

It is giving the incorrect result

JobID   RowNumber   FaceBook_Applications
202424  1           12

Now my questions are

What is the reason behind it?
How can I make it using SUM() function to lower the cost?

Any suggestion will be helpful.

Thanks for your time.

UPDATE

SELECT  AC.iJobID as JobID,
        ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
        SUM(CASE WHEN (RJ.sReferredFrom = 'F' AND RJ.iJobId_FK = 202424 AND RJ.iUserId_FK = 9550) THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM    tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE   AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

It is giving :

JobID   RowNumber   FaceBook_Applications
202424  1            8

UPDATE 2

SELECT iJobID_FK, sReferredFrom FROM tbUS_ReferJob WHERE iUserID_FK=9550 AND iJobID_FK=202424 AND sReferredFrom='F'

Result:

iJobID_FK   sReferredFrom
202424          F
202424          F

Andriy M · Accepted Answer · 2011-07-15 04:24:15Z

1

Take your second query (I'll repeat it here):

SELECT
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    SUM(CASE WHEN RJ.sReferredFrom = 'F' THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

and remove (or comment out) all the grouping & aggregating (ranking too, as it will serve no purpose in what I'm suggesting), like this, for example:

SELECT
    AC.iJobID as JobID, RJ.iUserId_FK,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,
    SUM(CASE WHEN RJ.sReferredFrom = 'F' THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

Now run the resulting query and have a look at how many Fs it produces. My point is, the result of the join most likely contains many duplicates, because both tables' subsets return more than one row and that results in the good old Cartesian product. A solution might consist either in cleaning up the duplicates in the original tables, if appropriate, or in making the WHERE condition more specific.

edited Jul 15, 2011 at 4:24

answered Jul 14, 2011 at 14:45

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Bertrand Over a year ago

+1 Also strongly recommend not using the old-fashion implicit join; while this same scenario could occur with an explicit INNER JOIN, the old-stype table,table,table scenario exposes one more a chance for failure: it lets you forget to put any join criteria at all.

Jacob · Accepted Answer · 2011-07-14 12:14:50Z

1

In your first query, you restrict the subquery with:

WHERE iUserId_FK = 9550

This restriction is missing in your second query. It should look like this:

SELECT  AC.iJobID as JobID,
        ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
        SUM(CASE WHEN RJ.sReferredFrom = 'F' THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM    tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE   AC.iJobID = RJ.iJobId_FK 
AND     AC.iJobID = 202424 
AND     RJ.iUserId_FK = 9550

edited Jul 14, 2011 at 12:14

answered Jul 14, 2011 at 12:04

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

3 Comments

Arindam Rudra Over a year ago

It is giving the result 8. I mentioned in my updated answer. Can you please have a look at it.

Jacob Over a year ago

@ARINDAM could you show us the result for this query: SELECT iJobID_FK, sReferredFrom FROM tbUS_ReferJob WHERE iUserID_FK=9550 AND iJobID_FK=202424 AND sReferredFrom=f

Arindam Rudra Over a year ago

I updated my question (UPDATE2) and it is giving the correct result.

il_guru · Accepted Answer · 2011-07-14 12:27:36Z

1

If i looked at it correctly, i think that you are missing a filter in the second query

AND iUserId_FK = 9550

The second query should be like the following:

SELECT
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    SUM(CASE WHEN RJ.sReferredFrom = 'F'  AND iUserId_FK = 9550 THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC,tbUS_ReferJob RJ
WHERE AC.iJobID = 202424 AND RJ.iJobId_FK = 202424
GROUP BY AC.iJobID

EDIT

i think there could be some problem with the join condition... try to make a clearer join before sum

SELECT
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    SUM(CASE WHEN RJ.sReferredFrom = 'F'   THEN 1 ELSE 0 END) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC
JOIN tbUS_ReferJob RJ
ON AC.iJobID =  RJ.iJobId_FK AND Rj.iUserId_FK = 9550
WHERE AC.iJobID = 202424
GROUP BY AC.iJobID

EDIT 2

SELECT
    AC.iJobID as JobID,
    ROW_NUMBER()OVER (ORDER BY AC.iJobID) AS RowNumber,     
    COUNT(*) AS FaceBook_Applications
FROM tbUS_AffiliateJobCount AC
JOIN tbUS_ReferJob RJ
ON AC.iJobID =  RJ.iJobId_FK AND Rj.iUserId_FK = 9550 AND RJ.sReferredFrom = 'F'
WHERE AC.iJobID = 202424
GROUP BY AC.iJobID

edited Jul 14, 2011 at 12:27

answered Jul 14, 2011 at 12:05

il_guru

8,5483 gold badges46 silver badges52 bronze badges

5 Comments

il_guru Over a year ago

how many rows are there in tbUS_AffiliateJobCount with iJobID = 202424?

il_guru Over a year ago

edit2 added also the 'F' filter to the join. This should fix it, i think

Arindam Rudra Over a year ago

Same problem it returns the count as 8.

il_guru Over a year ago

can you give the output of select * from tbUS_AffiliateJobCount where iJobId = 202424 ?

il_guru Over a year ago

Ok! Then the problem is the JOIN (implicit in your query, explicit in mine). The program join every rows in the first table (4) with the rows in the second table (2) making a 4x2 table which return a count of 8. Performing the query on the second table should solve in the faster way, another way is to do a COUNT(DISTINCT [XXX]) instead of COUNT(*) where [XXX] is the key in your second table (the one with the 2 records to count)

Collectives™ on Stack Overflow

Aggregate function related problem in SQL Server

3 Answers 3

1 Comment

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related