Group sproc...dont know how to join too

[StrangerInBeijing]

2 tables: (only including columns that matter)
tblIPtoCountry (countrycode, country)
tblSiteVisits (sessionid, countrycode)
SProc:

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

I want to return the Country, as well as the Count. Can only get Country by joining with tblIPtoCountry.

but when i try to join like this:

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 get this error:

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.

I tried the same thing in many variations, but no luck.

Thanks

[si_the_geek]

When you have aggregate functions (like Count or Sum) in your Select list, all non-aggregated fields need to be in the Group By list, eg:

...
GROUP BY  tblIPtoCountry.Country, tblSiteVisits.CountryCode
...

[szlamany]

Simply use the ORDINAL position to ORDER by...

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 2 DESC

That's why they gave us ORDINAL position

[StrangerInBeijing]

wished i replied last night at home and would have had an answer by now!

Thanks for the feedback (but i never heard about ORDINAL though)
This work fine....but not really...

ALTER PROCEDURE sp_GET_SiteVisitsByCountry
@startdate		DATETIME,
@enddate		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), @startdate, 112)
	AND CONVERT(CHAR(8),utctime, 112) <= CONVERT(CHAR(8), @enddate, 112)
GROUP BY  tblIPtoCountry.Country, tblSiteVisits.CountryCode
ORDER BY VisitCount DESC

SELECT  tblSiteVisits.countrycode, COUNT(sessionid) AS VisitCount
FROM tblSiteVisits

WHERE CONVERT(CHAR(8),utctime, 112) >= CONVERT(CHAR(8), @startdate, 112)
	AND CONVERT(CHAR(8),utctime, 112) <= CONVERT(CHAR(8), @enddate, 112)
GROUP BY  tblSiteVisits.countrycode
ORDER BY VisitCount DESC
GO

I forgot that in tblIPtoCountry there are many records for each countrycode + Country like this:

3278943684 3278943684 ad Andorra
3651939776 3651939791 ad Andorra
3645764045 3645764045 ae United Arab Emirates
3653744896 3653744959 ae United Arab Emirates
3590319104 3590319165 ae United Arab Emirates

Where the first two columns are the ip address ranges.

So the above query give me the correct country code and country now, but the COUNT are all messed up.

I figure my problem is with the join type, but heck, I tried everything in my limited tsql knowledge by now

[szlamany]

Why two SELECT's in that SPROC?

When you JOIN to a ...-to-many table you get multipled results on the primary table as well - you might need to make the "country" name be a:

,(SELECT TOP 1 ... FROM...)

sub-query instead.

[StrangerInBeijing]

I got one table, tblSiteVisits, with a column called "countrycode"
In a second table, called tblIpToCountry, I got countrycode and country.

I need to do a query that give the count of visists (sessionID column in tblSiteVisits) by country.

To get this result in a "countrycode" , "Count" format works fine, and for this I only need tblSiteVisits.

But I would Like the results in a "countrycode", "country", "count" format, and to do that I need to join with tblIpToCountry to get the country name for a country code.

my table setup is as follows:
tblSiteVisits :sessionID, userID, ipaddress, culture, countrycode
tblIPToCountry:rangeStart, rangeEnd, countrycode, country

the thing is that many ranges cover one country, so there will be multiple entries in tblIPtoCountry with the same countrycode, country combination.

[szlamany]

Well - it could be said that having the same country name repeated in a table with a country code is a problem with table design - there really should be a single table somewhere with one row for each country code that describes that country name.

Otherwise you run into this issue - you cannot JOIN without causing problems.

As I said that JOIN needs to be removed and that country name arrived at with the SUB-QUERY I indicated.

SELECT tblSiteVisits.CountryCode
, (Select Top 1 tblIPtoCountry.Country From tblIPtoCountry
	Where tblSiteVisits.countrycode = tblIPtoCountry.CountryCode)
, COUNT(sessionid) AS VisitCount
FROM tblSiteVisits
WHERE CONVERT(CHAR(8),utctime, 112) >= CONVERT(CHAR(8), @startdate, 112)
	AND CONVERT(CHAR(8),utctime, 112) <= CONVERT(CHAR(8), @enddate, 112)
GROUP BY tblSiteVisits.CountryCode
ORDER BY VisitCount DESC

[StrangerInBeijing]

denormalizing too much can be a bad thing...that table aint that big, it's pretty much static...so not much motivation creating 2 tables instead of one in a massive allready database. Or am I wrong?

Thanks for the help....will implement it in the morning.

[szlamany]

I have to agree that this whole normalization thing can go too far...

But if you are going to JOIN to this table a lot - and have to use a SUB-QUERY instead of a standard JOIN, there might be payoff.

Or it's might not matter at all!

I am not a normalization junkie - I do as far as needed and stop before it gets out of hand!