T-SQL - 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

T-SQL

Author

Topic

bluemagic74
Starting Member

15 Posts

Posted - 2011-12-06 : 19:55:59

Hi,

My table columns are – UserID, EntryVaule, EntryDateTime
One user can enter more than 1 entries in a day

I am trying to write a SQL query to find these.Kind of new to this.. Please let me know. Thank you.

AVG number of Total entries - total entries/total users
Frequency of Entries (count entries per user by week,month, year)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 21:00:17

[code]
select UserID, avg(EntryVaule)
from Entries
group by UserID

select UserID, dateadd(week, datediff(week, 0, EntryDateTime), 0), avg(EntryVaule)
from Entries
group by UserID, dateadd(week, datediff(week, 0, EntryDateTime), 0)

select UserID, dateadd(month, datediff(month, 0, EntryDateTime), 0), avg(EntryVaule)
from Entries
group by UserID, dateadd(month, datediff(month, 0, EntryDateTime), 0)

select UserID, dateadd(year, datediff(year, 0, EntryDateTime), 0), avg(EntryVaule)
from Entries
group by UserID, dateadd(year, datediff(year, 0, EntryDateTime), 0)

[/code]

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

bluemagic74
Starting Member

15 Posts

Posted - 2011-12-06 : 23:20:12

Thanks Khan!
throwing below error.

Msg 8117, Level 16, State 1, Line 2
Operand data type varchar is invalid for avg operator.

actually Entryvalue column has data like- 5, 1.4, 5000, 500/5000, 10/100, 10.5, 12 like this datatype is varchar(10).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 23:30:38

quote:
actually Entryvalue column has data like- 5, 1.4, 5000, 500/5000, 10/100, 10.5, 12 like this datatype is varchar(10).

then how do you calculate the average value for such data ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 23:38:11

oh sorry, i misunderstood your requirement.

this will give you the number of entry by day


select UserID, date = dateadd(week, datediff(week, 0, EntryDateTime), 0), cnt = count(*)
from   Entries
group by UserID, dateadd(week, datediff(week, 0, EntryDateTime), 0)

to get avg by month


select UserID, dateadd(month, datediff(month, 0, date), 0), avg(cnt)
from
(
    select UserID, date = dateadd(week, datediff(week, 0, EntryDateTime), 0), cnt = count(*)
    from   Entries
    group by UserID, dateadd(week, datediff(week, 0, EntryDateTime), 0)
) m
group by select UserID, dateadd(month, datediff(month, 0, date), 0)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources