CODEWITHSUNDEEP
sql-server
kin
kin

Reputation: 33

count transaction per day for each customer

How can I count transaction for each customer per day?

I have this table

badge_id    trans_date
badge1      2017-08-03 10:36:54.107
badge2      2017-08-07 10:39:00.550
badge2      2017-08-07 17:39:00.550
badge1      2017-08-07 12:31:28.467
badge1      2017-08-07 12:33:12.180
badge2      2017-08-08 12:46:02.250

the result should be like this

badge_id    trans_date  counter
badge1      2017-08-03      1
badge2      2017-08-07      2
badge1      2017-08-07      2
badge2      2017-08-08      1

the problem is when I use this query

SELECT CAST([trans_date] as DATE), 
COUNT(badge_id) AS counter 
FROM trans_details 
GROUP BY trans_date

It's just showing every date with 1 count

when I change to GROUP BY badge_id It showing this error

Msg 8120, Level 16, State 1, Line 1 Column 'trans_details.trans_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

what should i do? Thank you..

Upvotes: 0

Views: 4708

Answers (3)

Anagha
Anagha

Reputation: 918

You can also try this:

SELECT distinct badge_id, CAST(trans_date as DATE) as trans_date, 
COUNT(badge_id) over ( partition by badge_id,CAST(trans_date as DATE)) AS counter
FROM trans_details

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use DATE() function to extract date from the date time value and use that also in the GROUP BY.

Query

select [badge_id], 
CAST([trans_date] as date) as [trans_date], 
count([badge_id]) as [counter]
from [trans_details] 
group by [badge_id], CAST([trans_date] as date);

Upvotes: 2

AB_87
AB_87

Reputation: 1156

You need to cast date while grouping as well. Otherwise it is grouping on datetime which is unique in itself.

SELECT CAST([trans_date] as DATE), 
COUNT(badge_id) AS counter 
FROM trans_details 
GROUP BY CAST([trans_date] as DATE)

UPDATE: Sorry, just noticed that you want to group by badge_id as well. You can add badge_id to above query.

SELECT   CAST([trans_date] AS DATE) ,
         badge_id ,
         COUNT(badge_id) AS counter
FROM     trans_details
GROUP BY CAST([trans_date] AS DATE) ,
         badge_id;

Upvotes: 1

Related Questions