Help with query DISTINCT COUNT + AVG

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 2008 Forums

Transact-SQL (2008)

Help with query DISTINCT COUNT + AVG

Author

Topic

MrMainframe
Starting Member

2 Posts

Posted - 2009-08-21 : 20:02:19

With a table holding data
Userid Type
A X
B X
C Z
A X
C Z
Select distinct(userid), type, count(*) gives me
A X 3
B X 1
C Z 2
But how do I amend the query to list only userids that are greater than ( average count per type per user + 10%).
The average count for type X = 2 / 3 = 1.5 +10% = 1.65
Average count for type Z = 1 / 2 = 2 + 10% = 2.2
So I only want to retrieve row A X 2 but not row B X 1 and not row C Z 2.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-21 : 22:54:33

Look at HAVING and GROUP BY in book online

MrMainframe
Starting Member

2 Posts

Posted - 2009-08-25 : 14:41:52

thanks russell, I have already looked at the manual.
It is the specific syntax of the HAVING clause I am having trouble with.
SELECT AVG(COUNT(USERID)) at top of the query works and returns the average count
but when I try using this in the HAVING clause
SELECT DISTINCT(USERID)
GROUP BY USERID
HAVING COUNT(USERID) > AVG(COUNT(USERID))
it doesn't work - I get Group function is nested too deeply

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 17:11:35

I'm not exactly sure what you're going for here but maybe this will help you along.

Paste this code into SSMS and play with it.

CREATE TABLE #temp
(
Userid VARCHAR(15)
,[Type] VARCHAR(5)
)

INSERT INTO #temp
SELECT 'A','x'
UNION ALL SELECT 'B','x'
UNION ALL SELECT 'C','Z'
UNION ALL SELECT 'A','x'
UNION ALL SELECT 'C','Z'

WITH sumtype (type, countByType)
as
(
SELECT type, COUNT(*)
FROM #temp
GROUP BY type
),
avgUIDType AS
(
SELECT userid,type, COUNT(*) AS CountByUIDType
FROM #temp
GROUP BY userid,type
)
SELECT TOP 1 *, B.countByUidType /A.countByType AS Average
FROM sumtype A
JOIN avgUIDType B ON A.[type] = b.[type]
ORDER BY Average Desc

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources