Group by a case statement in a subquery

1

Hello,
I cannot work out what is wrong with my below code, I get an error message saying that the field date_created isn't in the select list because it is not contained in either an aggregate function or the group by clause. I keep on playing about with it but I get similar messages and I think I must be missing something key.

I am using a CTE subquery so that I can group on the case statement, I am happy to change the way I am doing this if this is not the most efficient way!

Can anyone see where I am going wrong?

Thanks in advance!

With Students as
(select date_created,
case
when nationality = 'England' then 'Domestic'
when nationality = 'Guernsey' then 'Domestic'
when nationality = 'Isle of Man' then 'Domestic'
when nationality = 'Jersey' then 'Domestic'
when nationality = 'Northern Ireland' then 'Domestic'
when nationality = 'Scotland' then 'Domestic'
when nationality = 'United Kingdom' then 'Domestic'
else 'International'
end as [Domestic or International],
count(crms_number) as [Total Prospects]
from ZZ_crms_student
where convert(date, date_created) > '2012-12-31'
and convert(date, date_created) < '2016-11-14'
and current_heat_status != 'dead')

Select YEAR(date_created), [Domestic or International], [Total Prospects]
from Students
GROUP BY YEAR(date_created), [Domestic or International]

2 
WITH Students
AS
(
    SELECT YEAR(date_created) AS YearCreated
        ,CASE
            WHEN nationality IN ('England', 'Guernsey', 'Isle of Man', 'Jersey'
                ,'Northern Ireland', 'Scotland', 'United Kingdom')
            THEN 'Domestic'
            ELSE 'International'
        END AS DorI
        ,crms_number AS Prospects
    FROM ZZ_crms_student
    WHERE date_created >= '20130101'
        AND date_created < '20161114'
        AND current_heat_status != 'dead'
)
SELECT YearCreated, DorI, COUNT(Prospects) AS TotalProspects
FROM Students
GROUP BY YearCreated, DorI
ORDER BY YearCreated, DorI;
1 Like
3

In your CTE, you don't have a group by but you have columns and an aggregate (count(crms_number) ). Without a group by, you can not mix aggregates and non-aggregates in your select list.

http://stackoverflow.com/questions/5920070/why-cant-you-mix-aggregate-values-and-non-aggregate-values-in-a-single-select

1 Like
4

Thanks, I see now what I was doing wrong and thank for tidying up the nationality section!

5

Review the changes that Ifor made in the CTE related to the date_created filter in the WHERE clause. This change is important to note because it allows SQL Server to utilize an index on the date_created column if it exists - whereas your version would not necessarily allow for that.

It also removes 2 function calls that are unnecessary - improving performance even more.

1 Like