Fruit Query - 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

General SQL Server Forums

New to SQL Server Programming

Fruit Query

Author

Topic

Blug
Starting Member

9 Posts

Posted - 2011-03-30 : 10:14:47

Hey guys, I was wondering how I would go about comparing an average with a count.

For example, I have 10 different kinds of fruits, each with there own ID, and then the type of fruit in another table with fruit ID.

FruitType
(FruitID)

Different Fruits
(FruitID, Fruit)

FruitID is just a number and then fruit is just apples and such. Fruit can contain multiple different kinds of fruits though. However the fruit ID in FruitType is primary key and different fruits the FruitID references FruitType.

How would I write a query to display fruit and the frequency in which it appears if the frequencies are higher than the average frequencies in which all fruit appears then it will be listed.

I'm kinda working with this at the moment..

select * from FruitType A inner join(select FruitID from Fruits B Group by Fruits having count(*) > avg(B.*)) B on A.FruitID = B.FruitID;

I know thats not right, Im getting confused by the bit where I confuse averages. Am I on the right path??

Thanks =D

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 10:28:23

First step would be to determine the 'base average' (ie. the average frequency for all fruits)...

maybe something like this:


; with cte(FruitId, Freq)
As (
	Select
		FruitId,
		Freq = COUNT(*)
	From Fruits
	Group By FruitId
)


Select * 
From cte A
Where Freq > (Select AvgFreq = AVG(Freq) From cte)

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 10:34:24

That doesn't even look like SQL Server. There's no purpose for the FruitType table and I don't know what an average of "bananas" mean. But let's start here.

SELECT df.Fruit,count(*) as freq
FROM DifferentFruits df
GROUP BY df.Fruit

Jim

P.S. We don't do homework here, but since you at least tried and posted what you tried, we're more than willing to help you figure it out.

Everyday I learn something that somebody else already knew

Blug
Starting Member

9 Posts

Posted - 2011-03-30 : 11:00:34

It's actually not homework, it was something which was mentioned in our lecture notes and he didn't really explain it very well and I really want to get good at this stuff so it becomes natural rather than having a hard time later on in the year when we have to create websites =(

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 11:15:10

That's a good idea, learn it now or it'll come back to bite you. Does Corey's query work for you? Do you have any questions about it?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources