Столбец недействителен в списке выбора, поскольку он не содержится в агрегатной функции или в предложении Group By.

У меня есть таблицы ниже:

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)

Теперь мне нужно получить общее количество пользователей в зависимости от местоположения и типа устройства за определенный месяц и год. Я попробовал ниже запрос для этого:

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);

Когда я пытаюсь запустить это, я получаю следующие ошибки:

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.

Если я не использую предложение Group By в приведенных выше операторах CASE, то также получаю ту же ошибку. Не могли бы вы сообщить мне, почему появилась эта ошибка и как я могу действовать здесь? Любая помощь приветствуется.


sql sql-server group-by
person SKJ    schedule 22.09.2014    source источник
comment
Это было бы более просто с таблицами для хранения регионов и сопоставлений региона со страной.   -  person Laurence    schedule 23.09.2014

Ответы (4)


arrow_upward
1
arrow_downward 

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

person M.Ali    schedule 22.09.2014
comment
Я думаю вместо ТОГДА 1 будет ТОГДА U.UserCount - person SKJ; 23.09.2014
comment
@SKJ Исправлено, извините за просмотр предоставленных данных, посмотрите сейчас. - person M.Ali; 23.09.2014

arrow_upward
1
arrow_downward

Похоже, вы можете избавиться от group by в операторах case и вместо этого суммировать внешний запрос.

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);

Пример SQLFiddle

person Laurence    schedule 22.09.2014
comment
Отсутствует оператор When во втором операторе Case. Отредактировал ответ - person SKJ; 23.09.2014
comment
@SKJ да, заметил это, когда делал SQL Fiddle - person Laurence; 23.09.2014

arrow_upward
1
arrow_downward

Ваш запрос безнадежно сложен для того, что вы хотите сделать. Я думаю, что условная агрегация ближе к тому, что вы действительно хотите:

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);
person Gordon Linoff    schedule 22.09.2014

arrow_upward
1
arrow_downward

Я думаю, что это дает вам желание, которое вы хотите:

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);
person pmbAustin    schedule 22.09.2014
comment
Когда я пытаюсь выполнить этот запрос, он выдает следующую ошибку: Невозможно выполнить агрегатную функцию для выражения, содержащего агрегат или подзапрос. - person SKJ; 23.09.2014
comment
Обратите внимание, что этот переработанный ответ намного длиннее, чем он должен быть для желаемого результата, но он основан непосредственно на вашем существующем запросе, поэтому, надеюсь, вы сможете увидеть, с чего вы начали, и увидеть, где я оказался, чтобы понять, как получить оттуда сюда. Сначала я просто создал Common Table Expression, чтобы позаботиться о соединениях. Затем я переработал ваши подзапросы, чтобы правильно фильтровать и группировать. Надеюсь, это поможет. - person pmbAustin; 23.09.2014