2 tables: (only including columns that matter)
tblIPtoCountry (countrycode, country)
tblSiteVisits (sessionid, countrycode)
SProc:
I want to return the Country, as well as the Count. Can only get Country by joining with tblIPtoCountry.Code:sp_GET_SiteVisitsByCountry_ByDate @date DATETIME AS SELECT tblSiteVisits.countrycode, COUNT(sessionid) AS VisitCount FROM tblSiteVisits WHERE CONVERT(CHAR(8),utctime, 112) = CONVERT(CHAR(8), @date, 112) GROUP BY tblSiteVisits.countrycode ORDER BY VisitCount DESC
but when i try to join like this:
I get this error:Code:sp_GET_SiteVisitsByCountry_ByDate @date DATETIME AS SELECT tblSiteVisits.CountryCode, tblIPtoCountry.Country, COUNT(sessionid) AS VisitCount FROM tblSiteVisits INNER JOIN tblIPtoCountry ON tblSiteVisits.countrycode = tblIPtoCountry.CountryCode WHERE CONVERT(CHAR(8),utctime, 112) = CONVERT(CHAR(8), @date, 112) GROUP BY tblIPtoCountry.Country ORDER BY VisitCount DESC
I tried the same thing in many variations, but no luck.Server: Msg 8120, Level 16, State 1, Procedure sp_GET_SiteVisitsByCountry_ByDate, Line 4
Column 'tblSiteVisits.countrycode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks




Reply With Quote