Need to get average count - 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

Need to get average count

Author

Topic

kaosarul
Starting Member

3 Posts

Posted - 2009-07-10 : 00:24:32

Dear Sir,

I have two clumn one is Date and other is subcategory.
I need to count average count of Subcategory according to per day.

Thanks,

kaosarul

mkaosarul

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-10 : 00:35:33

SELECT [Date], AVG([subcategory]) as av
FROM YourTable
GROUP BY [Date]
ORDER BY [Date];

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 01:48:56

you mean this ?


select	[date], Subcategory , average = avg(cnt)
from
(
	select	[date], Subcategory , cnt = count(*)
	from	yourtable
	group by [date], Subcategory 
) a
group by [date], Subcategory

KH
[spoiler]Time is always against us[/spoiler]

kaosarul
Starting Member

3 Posts

Posted - 2009-07-10 : 02:22:34

Dear Brother,

The details history is that ---- My database name is "CTC" and Table name is also "CTC". I have two columns. 1. Date 2. Member
And total members are 10 like zamshed, kaosarul, akram, afroz, sulfikar, smasum, siful, rabiul, masuma, shamima.
This members insert data daily into this database and in database their input are stacked in a clumn day by day. Also these members are not present every day. Because they do roaster duty. So some guys may absent for particulars two or three days a week.
I can count their total works by doing this query "SELECT DISTINCT(Member), count(Member) FROM CTC GROUP BY member". But now I need to count thier average work per day. That means {average = (Total work done by the member)/(Total day he/she present)}. So I need this average query.

Thanks,

Kaosarul

mkaosarul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 02:32:41

so is this what you want ?


select	[date], member, average = avg(cnt)
from
(
	select	[date], member, cnt = count(*)
	from	CTC 
	group by [date], member
) a
group by [date], member

KH
[spoiler]Time is always against us[/spoiler]

kaosarul
Starting Member

3 Posts

Posted - 2009-07-10 : 02:45:09

Dear Bro,

No its not working.

Thanks for prompt reply.

Kaosarul

mkaosarul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources