Calculating Average With count

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

General SQL Server Forums

New to SQL Server Programming

Calculating Average With count

Author

Topic

Anarking
Starting Member

13 Posts

Posted - 2007-05-02 : 07:21:55

Im trying to get the average Fuel Consumption for A Manufacturer that produces two or more cars, so far ive only been able to find all manufacturers Average Fuel consumption.

Heres what I have so far

Select aManufacturer.MName, avg(FuelCons)
From aCar
Join aBuilts On aBuilts.CName = aCar.CName
Join aManufacturer On aBuilts.MName = aManufacturer.MName
Group by aManufacturer.MName

This produces nearly all I want only I need to be able to get only the Manufacturers who produce two or more Cars, ive tried implementing a few Count statements but nothings working, any ideas?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 07:26:29

Somthing like this


Select aManufacturer.MName, avg(FuelCons)
From aCar
Join aBuilts On aBuilts.CName = aCar.CName
Join aManufacturer On aBuilts.MName = aManufacturer.MName
 Having Count(1) >1 
Group by aManufacturer.MName

Chirag

http://chirikworld.blogspot.com/

Anarking
Starting Member

13 Posts

Posted - 2007-05-02 : 07:28:29

Hmm, thats giving me this error

"Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'Group'."

Anarking
Starting Member

13 Posts

Posted - 2007-05-02 : 07:30:16

cool, got it when I changed it to this.

Select aManufacturer.MName, avg(FuelCons) As 'Average Fuel Consumption'
From aCar
Join aBuilts On aBuilts.CName = aCar.CName
Join aManufacturer On aBuilts.MName = aManufacturer.MName
Group by aManufacturer.MName
Having count(aBuilts.CName) > 1

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 07:35:13

opps..its was a silly mistake.. good to know you figured it out.. !!

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources