CODEWITHSUNDEEP
sqloracle-databasegroup-bysql-order-by
CamelCase
CamelCase

Reputation: 233

Difference in output because of group-by in SQL query

I have query which gives me the total distinct count of users having activity in a table for a week. It looks like this.

select count(distinct(t.employeeid)) as "User Count" 
from t, g  
where t.eventdate between '07-SEP-13' AND '14-SEP-13' 
and t.resourceid=g.id and g.resourcename = 'XYZ'`

I want to know count of users having activity each day for that same time period. So I modified the query like this.

select count(distinct(t.employeeid)) as "User Count", EXTRACT(DAY from t.eventdate) as      "Day" 
from t, g  
where t.eventdate between '07-SEP-13' AND '14-SEP-13' 
and t.resourceid=g.id and g.resourcename = 'XYZ' 
group by EXTRACT(DAY from t.eventdate)
order by EXTRACT(DAY from t.eventdate) ASC;`

But I am getting difference in the total counts. The 2nd query gives me about 80% more than the 1st query. Can someone please let me know what is going wrong and which query is having problems? Thanks in advance.

Upvotes: 1

Views: 49

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

You are counting distinct empoyeeid and you must have employeeids that have more than one eventdate. In your first query they would be counted as one person but in your second query they will be counted as one person per eventdate.

Upvotes: 1

Bob Provencher
Bob Provencher

Reputation: 450

It looks like you're getting a count of employee events in the second and employees in the first.

An employee will be counted as many times as they have events in a day.

Upvotes: 1

Related Questions