Столбцы в sql-запросе не группируются

У меня проблема со следующим запросом select sql. Колонки не агрегируются группой по команде.

SELECT 
 Dept.Name AS DeptName, COUNT (T.Id) AS TotalServiceNumber,
 (Case when SS.Status <> 'Resolved' then COUNT (T.Id) end) AS UnresolvedNumber,
 (Case when T.FixTime < '120' then COUNT(T.FixTime) end) AS ResolvedLessThanTwoHoursNumber,
 (Case when T.FixTime > '120' then COUNT(T.FixTime) end) AS ResolvedMoreThanTwoHoursNumber,

FROM
 dbo.Tickets AS T,
 dbo.ServiceStatuses AS SS,
 dbo.ComputerDesks AS Desk,
 dbo.Personnels AS Person,
 dbo.Departments AS Dept
WHERE
 SS.Id = T.ServiceStatusId
 AND T.ComputerDeskId = Desk.Id
 AND Desk.PersonnelId = Person.Id
 AND Person.DepartmentId = Dept.Id

GROUP BY
 Dept.Name, SS.Status, T.FixTime

Я получаю следующий результат:

DeptName | TotalServiceNr | UnresolvedNumber | LessThanTwo | MoreThanTwo 
DeptA | 8 | NULL | 8 | NULL 
DeptB | 1 | 1 | NULL | 1 
DeptC | 4 | NULL | NULL | 4 
DeptA | 38 | NULL | NULL | 38 
DeptB | 55 | NULL | 55 | NULL 
DeptC | 7 | NULL | 7 | NULL 
...

Ожидаемый результат:

DeptName | TotalServiceNr | UnresolvedNumber | LessThanTwo | MoreThanTwo 
DeptA | 46 | NULL | 8 | 38 
DeptB | 56 | 1 | 55 | NULL 
DeptC | 11 | NULL | 7 | 4

Что мне нужно изменить, чтобы получить ожидаемый результат?

3 ответа

попробуйте этот запрос:

SELECT 
 Dept.Name AS DeptName, COUNT (T.Id) AS TotalServiceNumber,
 sum(Case when SS.Status <> 'Resolved' then 1 else 0 end) AS UnresolvedNumber,
 sum(Case when T.FixTime <= '120' then 1 else 0 end) AS ResolvedLessThanTwoHoursNumber,
 sum(Case when T.FixTime > '120' then 1 else 0 end) AS ResolvedMoreThanTwoHoursNumber,

FROM
 dbo.Tickets AS T,
 dbo.ServiceStatuses AS SS,
 dbo.ComputerDesks AS Desk,
 dbo.Personnels AS Person,
 dbo.Departments AS Dept
WHERE
 SS.Id = T.ServiceStatusId
 AND T.ComputerDeskId = Desk.Id
 AND Desk.PersonnelId = Person.Id
 AND Person.DepartmentId = Dept.Id

GROUP BY
 Dept.Name


SELECT 
 Dept.Name AS DeptName
 , COUNT (T.Id) AS TotalServiceNumber
 ,COUNT(Case when SS.Status <> 'Resolved' then 1 ELSE NULL end) AS UnresolvedNumber
 ,COUNT(Case when T.FixTime < '120' then 1 ELSE NULL end) AS ResolvedLessThanTwoHoursNumber
 ,COUNT(Case when T.FixTime > '120' then 1 ELSE NULL end) AS ResolvedMoreThanTwoHoursNumber

FROM
 dbo.Tickets AS T INNER JOIN dbo.ServiceStatuses AS SS
ON SS.Id = T.ServiceStatusId
INNER JOIN dbo.ComputerDesks AS Desk
ON T.ComputerDeskId = Desk.Id
INNER JOIN dbo.Personnels AS Person
ON Desk.PersonnelId = Person.Id
INNER JOIN dbo.Departments AS Dept
Person.DepartmentId = Dept.Id
GROUP BY Dept.Name

Также используйте синтаксис ON для ваших объединений.


Попробуй это

SELECT TotalServiceNumber, SUM(UnresolvedNumber), SUM(ResolvedLessThanTwoHoursNumber), SUM(ResolvedMoreThanTwoHoursNumber)
FROM (
 SELECT 
 Dept.Name AS DeptName, COUNT (T.Id) AS TotalServiceNumber,
 (Case when SS.Status <> 'Resolved' then COUNT (C.Id) end) AS UnresolvedNumber,
 (Case when T.FixTime < '120' then COUNT(T.FixTime) end) AS ResolvedLessThanTwoHoursNumber,
 (Case when T.FixTime > '120' then COUNT(T.FixTime) end) AS ResolvedMoreThanTwoHoursNumber,

 FROM
 dbo.Tickets AS T,
 dbo.ServiceStatuses AS SS,
 dbo.ComputerDesks AS Desk,
 dbo.Personnels AS Person,
 dbo.Departments AS Dept
 WHERE
 SS.Id = T.ServiceStatusId
 AND T.ComputerDeskId = Desk.Id
 AND Desk.PersonnelId = Person.Id
 AND Person.DepartmentId = Dept.Id
 GROUP BY Dept.Name, SS.Status, T.FixTime
) GROUPED
GROUP BY
 TotalServiceNumber

licensed under cc by-sa 3.0 with attribution.