get avg from a Varchar field - 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)

get avg from a Varchar field

Author

Topic

moishe1927
Starting Member

1 Post

Posted - 2011-05-12 : 12:27:28

hi all, i have a table where users are filling in product reviews,
and 1 field "RECOMMENDED USE" is a Varchar type and can have values like "DAY,EVENING, or NIGHT."
now i want to know what most users have selected,

a simple query will like
Select Top 1 RecommendedUse,Count(*) ReviewCount From ProductReviews Where ProductID = 1 Group By RecommendedUse Order By ReviewCount

will give me what i need,
but i have more then 1 field that i need the most selected,(like RecommendedUse,RecommendedAge,and so on) and i don't want to make sub-query for every field

so whats the best way to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 13:35:40

Can you do something like this?


SELECT
	MAX(UseCount),
	MAX(AgeCount),
	MAX(SomethingElseCount)
FROM
(
	SELECT
		COUNT(*) OVER (PARTITION BY RecommendedUse) AS UseCount,
		COUNT(*) OVER (PARTITION BY RecommendedAge) AS AgeCount,
		COUNT(*) OVER (PARTITION BY SomethingElse) AS SomethingElseCount
	FROM
		ProductReviews
	WHERE
		ProductId = 1
)s

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-13 : 07:05:06

can use and see dense or rank function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources