CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

Issue with Function

SELECT imps.Interest_name as PopularInterests,
       imps.SUM(Count) AS Total_Count,
       ints.Vertical_Name 
FROM Impressions imps
  INNER JOIN Interests ints ON imps.Interest_name = ints.Interest_name
GROUP BY imps.Interest_name
HAVING imps.SUM(Count) > 10000;

The query gives me the error:

Lookup Error - SQL Server Database Error: Cannot find either column "imps" or the user-defined function or aggregate "imps.SUM", or the name is ambiguous

My tables are as follows:

Impressions:

Interest_Name   Count
 Geo_934293      1107
 Geo_934293      852
 Geo_934293      934
 Geo_8133957     1810
 Geo_8133957     909
 Geo_8133957     463
 Geo_8133957     736
 Geo_8133957     7000

Verticals:

Interest_Name   Vertical_Name
 Geo_934293         X
 Geo_8133957        Y

And the expected result would be:

Popular_Interest  Total_Count   Vertical_Name
   Geo_8133957      10918             Y

Not sure where I am going wrong? I assume it has to do with the SUM function.

Upvotes: 0

Views: 37

Answers (1)

dotnetom
dotnetom

Reputation: 24901

You need to use SUM(imps.Count) instead of imps.SUM(Count) (it is invalid syntax):

SELECT  
    imps.Interest_name as PopularInterests, 
    SUM(imps.Count) AS Total_Count, 
    ints.Vertical_Name 
FROM Impressions imps
    INNER JOIN Interests ints ON imps.Interest_name = ints.Interest_name
GROUP BY imps.Interest_name, ints.Vertical_Name 
HAVING SUM(imps.Count) > 10000;

Upvotes: 2

Related Questions