SQL Server 2005 error when grouping using subquery.

Question

Using SQL Server 2005 I'm trying to group based on a case statement with a subquery, but I'm getting an error ("Each GROUP BY expression must contain at least one column reference. "). I can work round it quite easily, but can anyone explain the error? I've got a column reference to #header.header.

create table #header (header int)
create table #detail (header int, detail int)

insert into #header values (1)
insert into #header values (2)
insert into #header values (3)

insert into #detail values (1, 1)
insert into #detail values (2, 1)

--error: Each GROUP BY expression must contain at least one column reference.
select case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header
group by case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end

--results I want
select hasrecords, count(*) from
(
    select case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header
) hasrecords
group by hasrecords

drop table #header
drop table #detail

[edit] Note (in response to comment) correlated and non-correlated subqueries:

--correlated
select header, case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header

--non-correlated
select #header.header, case when count(#detail.header) > 0 then 1 else 0 end hasrecords from #header left join #detail on #header.header = #detail.header group by #header.header

Answer 1

You are telling it to group by 1 or 0 when you need to give it an actual column to group by (header), not value.

So if I'm understanding right you are wanting a list of the headers and a count of their detail records?

This may work for you?

SELECT DISTINCT h.header, COUNT(d.detail) AS detail_count
FROM #header AS h
LEFT JOIN #detail AS d ON d.header = h.header
GROUP BY h.header, d.detail

With results like...

header   detail_count
1       1
2       1
3       0

Answer 2

To start, if we give the full error, it should read "Each GROUP BY expression must contain at least one column that is not an outer reference."

To understand the error, we need to clarify what is meant by an 'outer reference'

(Note: in this case it has nothing to do with inner or outer joins)

The inner and outer are in reference to the main query and it's subqueries. In this case the EXISTS is the subquery and it is a correlated subquery as it has an outer reference of #header.header, which is referencing the outer table #header, whereas any reference to #detail would be considered as inner references.

So in essence, because the CASE utilises a correlated subquery that references the outer query, then this fires the error state, beacuse this error message appears when you try to use only expressions in a GROUP BY clause that are interpreted as outer references.

Subqueries can be used in GROUP BY, but not correlated subqueries.

Confusingly, the same error can be generated by a non-subqueried, simpler query such as

select 
 case when header=1 then 1 
      else 0 
 end headeris1, 
 'constant' 
from #header 
group by case when header=1 then 1 else 0 end , 'constant'

or even replacing the constant with a @variable

Clear as mud?

Kev

Answer 3

Thanks.........

This is of GR8 Help