CODEWITHSUNDEEP
sqlsql-server
Bashar Abu Shamaa
Bashar Abu Shamaa

Reputation: 2029

SQL Server : Group by select statment give error

I have two tables:

I am trying to count the persons per country, but I received an error:

Invalid column name 'CountryName'.

Query:

SELECT  
    Count(HostAddress) as TotalNo,
    (select [ip-to-country].CountryName 
     from [ip-to-country] 
     where ((CAST(PARSENAME(HostAddress, 4) AS Bigint) * 256 * 256 * 256) + 
            (CAST(PARSENAME(HostAddress, 3) AS INT) * 256 * 256) + 
            (CAST(PARSENAME(HostAddress, 2) AS INT) * 256) + 
             CAST(PARSENAME(HostAddress, 1) AS INT))
            BETWEEN [ip-to-country].BegingIP AND [ip-to-country].EndIP) AS CountryName
FROM PersonTBL 
GROUP BY
   CountryName

I also tried :

SELECT  
    Count(HostAddress) as TotalNo,
    (SELECT [ip-to-country].CountryName 
     FROM [ip-to-country] 
     WHERE 
        ((CAST(PARSENAME(HostAddress, 4) AS Bigint) * 256 * 256 * 256) + 
         (CAST(PARSENAME(HostAddress, 3) AS INT) * 256 * 256) +
         (CAST(PARSENAME(HostAddress, 2) AS INT) * 256) +
         CAST(PARSENAME(HostAddress, 1) AS INT)) BETWEEN [ip-to-country].BegingIP AND [ip-to-country].EndIP) as CountryName
FROM PersonTBL 
GROUP BY 
    (SELECT [ip-to-country].CountryName 
     FROM [ip-to-country] 
     WHERE 
        ((CAST(PARSENAME(HostAddress, 4) AS Bigint) * 256 * 256 * 256) + 
         (CAST(PARSENAME(HostAddress, 3) AS INT) * 256 * 256) +
         (CAST(PARSENAME(HostAddress, 2) AS INT) * 256) +
         CAST(PARSENAME(HostAddress, 1) AS INT)) BETWEEN [ip-to-country].BegingIP AND [ip-to-country].EndIP) as CountryName

But I received another error :

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Can any one help me to group the users by country ?

Upvotes: 2

Views: 104

Answers (3)

Lamak
Lamak

Reputation: 70678

You can't use a column alias directly on the GROUP BY if you just define that alias, you either use the same expression on the GROUP BY or use a derived table or a CTE:

Derived Table:

SELECT  COUNT(HostAddress) AS TotalNo, 
        CountryName
FROM (  SELECT  HostAddress,
                (SELECT [ip-to-country].CountryName 
                 FROM [ip-to-country] 
                 WHERE 
                 ((CAST(PARSENAME(HostAddress, 4) AS BIGINT)*256*256*256) + 
                 (CAST(PARSENAME(HostAddress, 3) AS INT)*256*256)
                  + (CAST(PARSENAME(HostAddress, 2) AS INT)*256) 
                  + CAST(PARSENAME(HostAddress, 1) AS INT))
                 BETWEEN  [ip-to-country].BegingIP and [ip-to-country].EndIP) AS CountryName
         FROM PersonTBL) T
 GROUP BY CountryName

CTE (SQL Server 2005+):

;WITH CTE AS
(
    SELECT  HostAddress,
            (SELECT [ip-to-country].CountryName 
             FROM [ip-to-country] 
             WHERE 
             ((CAST(PARSENAME(HostAddress, 4) AS BIGINT)*256*256*256) + 
             (CAST(PARSENAME(HostAddress, 3) AS INT)*256*256)
              + (CAST(PARSENAME(HostAddress, 2) AS INT)*256) 
              + CAST(PARSENAME(HostAddress, 1) AS INT))
             BETWEEN  [ip-to-country].BegingIP and [ip-to-country].EndIP) AS CountryName
     FROM PersonTBL
)
SELECT  COUNT(HostAddress) AS TotalNo, 
        CountryName
FROM CTE
GROUP BY CountryName

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271161

In SQL Server, you can't use column aliases in the group by clause. Here is an alternative:

with cte as (
     SELECT HostAddress,
            (select [ip-to-country].CountryName
             from [ip-to-country]
             where ((CAST(PARSENAME(HostAddress, 4) AS Bigint)*256*256*256) + 
                    (CAST(PARSENAME(HostAddress, 3) AS INT)*256*256) +
                    (CAST(PARSENAME(HostAddress, 2) AS INT)*256) +
                     CAST(PARSENAME(HostAddress, 1) AS INT)
                   ) between  [ip-to-country].BegingIP and [ip-to-country].EndIP
            ) as CountryName
     FROM PersonTBL
    )
select count(HostAddress) as TotalNo, CountryName
from cte
Group By CountryName;

Upvotes: 2

simon at rcl
simon at rcl

Reputation: 7344

You're missing a ). Try:

SELECT  Count(HostAddress) as TotalNo,
 (select [ip-to-country].CountryName from [ip-to-country] where 
 ((CAST(PARSENAME(HostAddress, 4) AS Bigint)*256*256*256) + 
(CAST(PARSENAME(HostAddress, 3) AS INT)*256*256)
 + (CAST(PARSENAME(HostAddress, 2) AS INT)*256) 
 + CAST(PARSENAME(HostAddress, 1) AS INT))
between  [ip-to-country].BegingIP and [ip-to-country].EndIP) as CountryName
)
 FROM PersonTBL 
 Group By 
 CountryName

I haven't got your table so I'm not sure it'll work, but it's more likely to!

Upvotes: -3

Related Questions