x
Solved

# How to group by date?

Posted on 2011-09-21
Medium Priority
173 Views
In the following, how do I get a count for each unique day entry on a ticketid for an employee?

In the example below, empid=1 and ticketid=7 would have a count of 3, since it was worked on three different days.  However, empid=1 and ticketid=9 is just one day, since both entries were on 9/17. empid=3 and ticketid=9 is also one day since both entries were on 9/17 as well.
``````Id   empid   ticketid   datecomplete              ticketsession
4      1         9      2011-09-17 9:12:24.000        3
4      1         9      2011-09-17 9:12:24.000        3
10     1         7      2011-09-15 9:12:24.000        7
19     1         7      2011-09-16 9:12:24.000        7
20     1         7      2011-09-17 11:17:24.000        7
21     3         9      2011-09-17 9:12:24.000        3
21     3         9      2011-09-17 10:12:24.000        3
``````
0
Question by:brettr
• 3
• 3

LVL 32

Accepted Solution

Ephraim Wangoya earned 2000 total points
ID: 36576102
try
``````select empid, CONVERT(varchar(10), datecomplete, 101) datecomplete
from table1
group by empid, CONVERT(varchar(10), datecomplete, 101)
``````
0

Author Comment

ID: 36576173
Sorry - accepted too soon.  What I want is the count of 3 for empid=1 and ticketid=7.  The above query keep them on separate rows and doesn't provide a count.
0

LVL 32

Expert Comment

ID: 36576201

``````select empid, count(empid) [Employees], count(tickectid) [tickectid], CONVERT(varchar(10), datecomplete, 101) datecomplete
from table1
group by empid, tickectid, CONVERT(varchar(10), datecomplete, 101)
``````
0

Author Comment

ID: 36576254
That just counts each single row.  So instead of getting 3 for 9/15, 9/16 and 9/17, which were all on the same ticket, you can 1 for each.
0

LVL 32

Expert Comment

ID: 36576440
Right, you only need one count but group by the two fields

``````select empid, count(tickectid) [Tickect Count], CONVERT(varchar(10), datecomplete, 101) datecomplete
from table1
group by empid, tickectid, CONVERT(varchar(10), datecomplete, 101)
``````
0

Author Comment

ID: 36576457
ok, thanks.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.