Averaging a Count in a Table - 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)

Averaging a Count in a Table

Author

Topic

invisible777
Starting Member

10 Posts

Posted - 2007-05-03 : 01:21:21

Row goes ItemOne, ItemTwo, CustID

If I have a bunch of rows that look like this, how can I group and quantify the CustID get an overall average of how many times it appears? In other words, 003 appears 4 times, 004 appears 2 times, 5 appears 1 time. How could I code it to group and average 4, 2, 1?

('ItemOne','ItemTwo',003)
('ItemOne','ItemTwo',003)
('ItemOne','ItemTwo',004)
('ItemOne','ItemTwo',004)
('ItemOne','ItemTwo',005)
('ItemOne','ItemTwo',003)
('ItemOne','ItemTwo',003)

dfiala
Posting Yak Master

116 Posts

Posted - 2007-05-03 : 01:29:02

Select CustID, Count(*) Total FROM SomeTable Group By CustID

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 01:32:08

[code]-- Prepare sample data
declare @t table
(
ItemOne varchar(10),
ItemTwo varchar(10),
CustID varchar(10)
)

insert @t
select 'ItemOne','ItemTwo','003' union all
select 'ItemOne','ItemTwo','003' union all
select 'ItemOne','ItemTwo','004' union all
select 'ItemOne','ItemTwo','004' union all
select 'ItemOne','ItemTwo','005' union all
select 'ItemOne','ItemTwo','003' union all
select 'ItemOne','ItemTwo','003'

-- Count
select CustID, count(*) as cnt
from @t
group by CustID

-- Average
Select Avg(cnt) as Average
from
(select CustID, count(*) as cnt
from @t
group by CustID
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

invisible777
Starting Member

10 Posts

Posted - 2007-05-03 : 01:36:45

wow quick replies... thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources