How to count distinct records

user_947322 0
Viewed 36139

Could anybody please help me on SQL command?

I have a table (tbl_sActivity) that have below data:

user_id | client_id | act_status |
1           |     7        |      cold     |
1           |     7        |    dealed   |
22         |     5        |      cold     |
1           |     6        |      cold     |
1           |     6        |     warm    |
1           |     6        |      hot       |
1           |     6        |    dealed   |
1           |     8        |     warm    |
1           |     8        |    dealed   |
21         |     4        |     warm    |
21         |     4        |    dealed   |

The out put should be

user_id | Count_C_id |
 1          |     3             |
 21        |     1             |
 22        |     1             |

I've searched from net and learnt that MS ACCESS cannot use COUNT(DISTINCT) function. So I'm stuck at this stage for days.

ms-access sql
2 Answers

Recommendation is to make query without using sub-query.

Please find the below code which will be faster and accurate then subquery.

// Temp Table

CREATE TABLE #TempStudent(userId  int, c_id int , Name  varchar(MAX) )

SELECT max(userid) as UserId, count(c_id) as C_ID from #TempStudent 
GROUP BY userId
user_38614840
Related