Group by error? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by error?

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:40:25

I am having a group by error, Can anyone spot it?


Select * from
(
SELECT    C.Name
          ,ci.District
          ,count(ci.Name) as NumberofCities

FROM Country C
inner join City ci
on C.Code = ci.countryCode
)x
Where x.NumberofCities>=10
group by t.NumberofCities;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:43:11

yup. you cant use * when you use GROUP BY. you need to apply aggregate functions on all columns which are not specified in GROUP BY.
Actually your code doesnt make much sense. why are tryingto GROUP BY count calaculated in the derived table? can you explain what you're trying to achive?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-09 : 12:44:16

SELECT C.Name
,ci.District
,count(ci.Name) as NumberofCities

FROM Country C
inner join City ci
on C.Code = ci.countryCode
Where x.NumberofCities>=10
group by C.Name ,ci.Districtt

Jim

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:45:55

Whoops sorry I am new to SQL sorta got thrown in the fire in my new job :(
I am trying to get the name of the country, the name of the district, and the number of cities in each district for each district
that has 10 or more cities in it. but it for some reason I am missing something. I really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:47:00

quote:
Originally posted by jimf

SELECT C.Name
,ci.District
,count(ci.Name) as NumberofCities

FROM Country C
inner join City ci
on C.Code = ci.countryCode
Where x.NumberofCities>=10
group by C.Name ,ci.Districtt

Jim

you cant use aliases in where like this. you need to do it like below

SELECT *
FROM
(
SELECT    C.Name
          ,ci.District
          ,count(ci.Name) as NumberofCities

FROM Country C
inner join City ci
on C.Code = ci.countryCode
group by C.Name          
,ci.Districtt 
)x
Where x.NumberofCities>=10

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:53:11

Visakh,

running that query i receive an error of this:
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

The query looks good but no go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:56:44

can you show your full query? i didnt get that error.

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:58:25

whoops it was an error on my part i had a misspelling error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 13:00:27

has it run successfully now?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 13:08:02

Yes thank you. How long have you been working with sql?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 13:11:56

About 5 yrs.why?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 13:13:29

YOur pretty good at it, just trying to see how long it will take me to get to a knowledgeable point with sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 13:21:33

Thanks for compliements

I would suggest you start off leraning sql from books online. use sample queries given there and understand the concepts and apply it in your solutions. whenever you've doubt post it in forums like here. Understand and learn solutions provided.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources