CODEWITHSUNDEEP
sqlsql-server-2005
Mark
Mark

Reputation: 717

SQL 2005: How to use GROUP BY with a sub query

The following very simple query 

select distinct guid, browser_agent
from tblMyGlossary
where browser_agent is not null

provides the following results:

guid         browser_agent
367DE2B8-88A5-4DA9-ACBB-C0864493DC1F Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727)
5DCB918E-DA56-4545-A4E3-D09B1B803422 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727)
998B8F37-2C9A-49EB-AA0B-CF88C4CC7BDF Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5
A0DD3BCB-E8A9-4434-A869-C343FB21F993 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727)

Ii want to be able to count the number of unique browser_agent strings, so I am performing the following query:

select browser_agent, count(browser_agent) as 'count'
from
(
 select distinct guid, browser_agent
 from tblMyGlossary
 where browser_agent is not null
)
group by browser_agent
order by 'count' desc;

Problem is SQL 2005 is complaining:

Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'group'.

Can anyone shed any light on how to resolve this please? I've run out of ideas.

Many thanks,

Mark

Upvotes: 1

Views: 2432

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

You need to alias your derived table.

select browser_agent, count(browser_agent) as 'count'
from
(
    select distinct guid, browser_agent
    from tblMyGlossary
    where browser_agent is not null
) a
group by browser_agent
order by 'count' desc;

Upvotes: 4

Related Questions