Group sproc...dont know how to join too

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

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

---

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:

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

Quote:

---

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

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:

Code:

---

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

---

Simply use the ORDINAL position to ORDER by...

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

---

That's why they gave us ORDINAL position ;)

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

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

Code:

---

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:

Quote:

---

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

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.

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.

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.

Code:

---

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

---

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. :)

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!