Reputation: 5957

Column is invalid in the select list because it is not contained in an aggregate function or in Group By clause

I have below tables:

Create Table Country(CountryID int primary key, CountryName nvarchar(100) not null)
Create Table DeviceType(DeviceTypeID int primary key, DeviceTypeName nvarchar(100) not null)
Create Table UserStat
(
LocalID int primary key identity(1,1),
TimePeriod datetime not null,
CountryID int not null,
DeviceTypeID int not null,
UserCount int not null,
CONSTRAINT [FK_UserStat_Country] FOREIGN KEY (CountryID) REFERENCES [dbo].[Country] (CountryID),
CONSTRAINT [FK_UserStat_DeviceType] FOREIGN KEY (DeviceTypeID) REFERENCES [dbo].[DeviceType] (DeviceTypeID))

Insert into Country values (1, 'India')
Insert into Country values (2, 'USA')

Insert Into DeviceType values (1, 'Mobile')
Insert Into DeviceType values (2, 'Desktop')

Insert into UserStat values (CAST(DATEFROMPARTS(2014,9,1) AS datetime),1,1,9999)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,9,1) AS datetime),1,2,10000)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,9,1) AS datetime),2,1,20000)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,9,1) AS datetime),2,2,19999)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,8,1) AS datetime),1,1,50000)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,8,1) AS datetime),1,2,60000)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,8,1) AS datetime),2,1,70000)
Insert into UserStat values (CAST(DATEFROMPARTS(2014,8,1) AS datetime),2,2,80000)

Now, I need to get total users based on location and device type for a particular month and year. I tried below query for this:

Declare @region nvarchar(50)='Both'
Declare @calendarYear int = 2014

SELECT  YEAR(U.TimePeriod) AS [Year],
        MONTH(U.TimePeriod) AS [Month],
        CASE 
            WHEN @region = 'India' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName = 'India' AND D.DeviceTypeName = 'Mobile' GROUP BY C.CountryName, D.DeviceTypeName)
            WHEN @region = 'USA' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName = 'USA' AND D.DeviceTypeName = 'Mobile' GROUP BY C.CountryName, D.DeviceTypeName)
            WHEN @region = 'Both' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName IN ('India', 'USA') AND D.DeviceTypeName = 'Mobile' GROUP BY C.CountryName, D.DeviceTypeName)
            ELSE 0
        END AS [MobileUsers],
        CASE 
        WHEN @region = 'India' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName = 'India' AND D.DeviceTypeName = 'Desktop' GROUP BY C.CountryName, D.DeviceTypeName)
            WHEN @region = 'USA' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName = 'USA' AND D.DeviceTypeName = 'Desktop' GROUP BY C.CountryName, D.DeviceTypeName)
            WHEN @region = 'Both' THEN (SELECT SUM(U.UserCount) WHERE C.CountryName IN ('India', 'USA') AND D.DeviceTypeName = 'Desktop' GROUP BY C.CountryName, D.DeviceTypeName)
            ELSE 0
        END AS [DesktopUsers]
FROM dbo.UserStat AS U WITH (NOLOCK)
Join dbo.Country AS C WITH (NOLOCK) ON C.CountryID=U.CountryID 
Join dbo.DeviceType AS D WITH (NOLOCK) ON D.DeviceTypeID=U.DeviceTypeID 
WHERE YEAR(U.TimePeriod) = @calendarYear
GROUP BY YEAR(U.TimePeriod), MONTH(U.TimePeriod);

When I try to run this, I am getting below errors:

Column 'dbo.Country.CountryName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'dbo.DeviceType.DeviceTypeName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I don't use Group By clause in above CASE statements, then also I am getting same error, Can you please let me know why this error has shown and how can I proceed here? Any help is appreciated.

Upvotes: 1

Answers (4)

pmbAustin

Reputation: 3970

I think this gives you want you want:

DECLARE @region varchar(10);
DECLARE @calendarYear int;
SET @calendarYear = 2014
SET @region = 'USA'

;WITH Data AS (
SELECT U.*, C.CountryName, D.DeviceTypeName
FROM dbo.UserStat AS U WITH (NOLOCK)
INNER JOIN dbo.Country AS C WITH (NOLOCK) ON C.CountryID=U.CountryID 
INNER JOIN dbo.DeviceType AS D WITH (NOLOCK) ON D.DeviceTypeID=U.DeviceTypeID 
) 
SELECT YEAR(d1.TimePeriod) AS [Year],
       MONTH(d1.TimePeriod) AS [Month],
       CASE 
           WHEN @region = 'India' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                         WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                           AND d2.CountryName = 'India' AND d2.DeviceTypeName = 'Mobile' GROUP BY YEAR(d2.TimePeriod))
           WHEN @region = 'USA' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                       WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                         AND d2.CountryName = 'USA' AND d2.DeviceTypeName = 'Mobile' GROUP BY YEAR(d2.TimePeriod))
           WHEN @region = 'Both' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                        WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                          AND d2.CountryName IN ('India', 'USA') AND d2.DeviceTypeName = 'Mobile' GROUP BY YEAR(d2.TimePeriod))
           ELSE 0
       END AS [MobileUsers],
       CASE 
       WHEN @region = 'India' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                     WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                       AND d2.CountryName = 'India' AND d2.DeviceTypeName = 'Desktop' GROUP BY d2.CountryName, d2.DeviceTypeName)
           WHEN @region = 'USA' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                       WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                         AND d2.CountryName = 'USA' AND d2.DeviceTypeName = 'Desktop' GROUP BY d2.CountryName, d2.DeviceTypeName)
           WHEN @region = 'Both' THEN (SELECT SUM(d2.UserCount) FROM Data d2 
                                        WHERE YEAR(d2.TimePeriod) = YEAR(d1.TimePeriod) AND MONTH(d2.TimePeriod) = MONTH(d1.TimePeriod) 
                                          AND d2.CountryName IN ('India', 'USA') AND d2.DeviceTypeName = 'Desktop' GROUP BY d2.DeviceTypeName)
           ELSE 0
       END AS [DesktopUsers]
  FROM Data d1
WHERE YEAR(d1.TimePeriod) = @calendarYear
GROUP BY YEAR(d1.TimePeriod), MONTH(d1.TimePeriod);

Upvotes: 1

M.Ali

Reputation: 69514

Declare @region nvarchar(50)='BOTH'
Declare @calendarYear int = 2014

SELECT   YEAR(U.TimePeriod)  AS [Year]
        ,MONTH(U.TimePeriod) AS [Month]
        ,SUM(CASE WHEN D.DeviceTypeName = 'Mobile'  THEN U.UserCount ELSE NULL END) AS [MobileUsers]
        ,SUM(CASE WHEN D.DeviceTypeName = 'Desktop' THEN U.UserCount  ELSE NULL END) AS [DesktopUsers]

FROM dbo.UserStat   AS U WITH (NOLOCK)
Join dbo.Country    AS C WITH (NOLOCK) ON C.CountryID    = U.CountryID 
Join dbo.DeviceType AS D WITH (NOLOCK) ON D.DeviceTypeID = U.DeviceTypeID 
WHERE YEAR(U.TimePeriod) = @calendarYear
 AND (  
        (C.CountryName = @region AND @region <> 'Both')
        OR
        (@region = 'Both' AND C.CountryName IN ('India','USA'))
      )
GROUP BY YEAR(U.TimePeriod), MONTH(U.TimePeriod);

`SQL FIDDLE`

Upvotes: 1

Gordon Linoff

Reputation: 1269623

Your query is hopelessly complicated for what you want to do. I think conditional aggregation is closer to what you really want:

SELECT  YEAR(U.TimePeriod) AS [Year],
        MONTH(U.TimePeriod) AS [Month],
        SUM(CASE WHEN D.DeviceTypeName = 'Mobile' THEN U.UserCount ELSE 0 END) as MobileUsers,
        SUM(CASE WHEN D.DeviceTypeName = 'DeskTop' THEN U.UserCount ELSE 0 END) as DeskTopUsers
FROM dbo.UserStat AS U WITH (NOLOCK) Join
     dbo.Country AS C WITH (NOLOCK)
     ON C.CountryID = U.CountryID Join
     dbo.DeviceType AS D WITH (NOLOCK)
     ON D.DeviceTypeID = U.DeviceTypeID 
WHERE YEAR(U.TimePeriod) = @calendarYear AND
      (@region = 'Both' or @region = C.CountryName)
GROUP BY YEAR(U.TimePeriod), MONTH(U.TimePeriod);

Upvotes: 1

Laurence

Reputation: 10976

It looks like you can get rid of the group bys in the case statements, and instead sum over the outer query.

select
    year(u.TimePeriod) AS [Year],
    month(u.TimePeriod) AS [Month],
    sum(case
        when d.DeviceTypeName = 'Mobile' and ((
                @region in ('India', 'Both') and 
                c.CountryName = 'India'
            ) or (
                @region in ('USA', 'Both') and
                c.CountryName = 'USA'
            )) then u.UserCount
        else
            0
        end) as [MobileUsers],
    sum(case 
            when d.DeviceTypeName = 'Desktop' and ((
                @region in ('India', 'Both') and 
                c.CountryName = 'India'
            ) or (
                @region in ('USA', 'Both') and
                c.CountryName = 'USA'
            )) then u.UserCount
        else
            0
        end) as [DesktopUsers]
from
    dbo.UserStat as u with (nolock)
        inner Join 
    dbo.Country as c with (nolock) 
        on c.CountryID = u.CountryID
        inner Join 
    dbo.DeviceType as d with (nolock) 
        on d.DeviceTypeID = u.DeviceTypeID 
where
    year(u.TimePeriod) = @calendarYear
group by
    year(u.TimePeriod),
    month(u.TimePeriod);

Example SQLFiddle

Upvotes: 1

Column is invalid in the select list because it is not contained in an aggregate function or in Group By clause

Answers (4)

SQL FIDDLE

Related Questions

`SQL FIDDLE`