CODEWITHSUNDEEP
sqlt-sqlgroup-by
cbp
cbp

Reputation: 25628

Group by a subquery

What is the best way of making the following T-SQL query valid:

select
    count(*),
    (... a subquery that returns one result ...) as [Bar]
from Foo foo
group by [Bar]

Upvotes: 4

Views: 12842

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

SELECT COUNT(*), 
       (SELECT TOP 1 name 
        FROM   sys.objects 
        ORDER  BY object_id%number) name 
FROM   master..spt_values 
WHERE  number > 0 
GROUP BY (SELECT TOP 1 name 
            FROM   sys.objects 
            ORDER  BY object_id%number)

Gives the error

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Maybe someone else can answer why this is not allowed. A couple of ways which are valid

SELECT COUNT(*), 
       oa.name 
FROM   master..spt_values 
       OUTER APPLY (SELECT TOP 1 name 
                    from   sys.objects 
                    ORDER  BY object_id%number) oa 
WHERE  number > 0 
GROUP  BY oa.name

and

;WITH T AS
(
SELECT number, 
       (SELECT TOP 1 name 
        from   sys.objects 
        ORDER  BY object_id%number) name 
FROM   master..spt_values 
WHERE  number > 0  
)
SELECT COUNT(*), 
       name 
FROM T 
GROUP BY name

Upvotes: 7

Related Questions