CODEWITHSUNDEEP
sql-server
gsiradze
gsiradze

Reputation: 4733

Group by returns wrong result

I have that query:

select e.UserId as UserId, COUNT(*) as [Registrations] from dbo.EventLog as e 
where e.EventTypeId = 3 and e.Value is not null
group by UserId

and example data returned by it is:

UserId Registrations

1   37

3   1

10  2

12  69

13  22

14  5

But I want to select timestamp as well. for that I'm adding e.[TimeStamp] in select and it says:

Column 'dbo.EventLog.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I'll add e.[TimeStamp] in group by result is:

UserId Registrations TimesTamp

1   1   2013-01-09 13:29:19.143

25  1   2013-01-09 13:52:37.687

27  1   2013-01-09 15:00:03.147

27  1   2013-01-09 16:12:01.000

27  1   2013-01-10 16:00:53.757

34  1   2013-01-10 17:13:52.000

mention that registrations with id 1 are in whole row instead of for example 37, 2 etc.

Upvotes: 0

Views: 205

Answers (3)

Ganesh
Ganesh

Reputation: 6127

Following is the reasons. Why you get such result.

You are grouping by user Id as well as Timestamp

  • There can be multiple userId with your condition(e.EventTypeId = 3 and e.Value is not null) agreed but if you group by timestamp as well there is only one user which satisfies that condition and has a timestamp
  • If you can put average(timestamp) or min or max in select if you want to have all the registrations.

Good luck

Upvotes: 1

Hans Kesting
Hans Kesting

Reputation: 39265

A timestamp will probably be different for each row, so which timestamp do you actually mean from the set that shares a UserId?

What you could do is find the first timestamp:

select 
   e.UserId as UserId, 
   COUNT(*) as [Registrations],
   Min(e.Timestamp) as [First Timestamp]
from dbo.EventLog as e 
where e.EventTypeId = 3 and e.Value is not null
group by UserId

or the last one, using max()

Upvotes: 3

Jonas Høgh
Jonas Høgh

Reputation: 10874

What do you expect the timestamp column to contain for the grouping representing the 37 registrations of user 1? Since there are also 37 timestamp values, you need an aggregate function for this to be meaningful. E.g. if you want the timestamp of the latest registration for each user, use the MAX function.

Upvotes: 0

Related Questions