CODEWITHSUNDEEP
mysqlsqldatabase
viyancs
viyancs

Reputation: 2339

How to get count of two fields unique id in MySQL?

I have problem when I want to select query with 2 field and that field is unique id then I try to create table like this

created_at             user_id 
2013-01-05             1
2013-01-20             1
2013-01-23             2
2013-01-28             1
2013-02-01             3
2013-02-04             2

look that is any user_id is not unique and created_at is not unique too, so I have create query select like this code

SELECT MONTH( created_at ) AS month 
     , count( user_id ) AS total 
FROM applies where YEAR(created_at) = 2013 
GROUP BY month

this is the result 

month             total
1                 4
2                 2

in the month January (1) actually I have 3 users but that is still show 4 users because count not unique user_id how can I am filter that user_id to unique?

Upvotes: 1

Views: 120

Answers (1)

Himanshu
Himanshu

Reputation: 32602

To not count duplicate user_id use DISTINCT keyword for that:

SELECT MONTH(created_at) AS Month
      ,COUNT(DISTINCT User_id) AS Total
FROM Table1
GROUP BY MONTH(created_at)

Result:

| MONTH | TOTAL |
-----------------
|     1 |     2 |
|     2 |     2 |

See this SQLFiddle

Upvotes: 2

Related Questions