CODEWITHSUNDEEP
mysql
I-M-JM
I-M-JM

Reputation: 15950

wrong count in query

I have a table whose structure is as follows:

id  int
userid  int
status  enum ('pending','approved')
dop     datetime

Data is as:

id          userid               status             dop
 1            24                 pending            2011-02-14 06:41:32
 2            24                 pending            2011-02-15 23:02:45

When I fire the following query:

SELECT count( userid )
FROM t1
WHERE STATUS = 'pending'
GROUP BY userid

It's giving me count as '2', which is wrong, can anyone tell me what's wrong here? and how to get real count as 1

Upvotes: 1

Views: 3241

Answers (6)

SubniC
SubniC

Reputation: 10317

You sould use the COUNT(DISTINCT()) construction, it allow you to count the diferent values not NULL (docu)

Try this sentence:

SELECT count( DISTINCT( userid ) )
FROM t1
WHERE STATUS = 'pending'
GROUP BY userid

HTH!

Upvotes: 0

Simeon
Simeon

Reputation: 5579

The group by statement is executed after the count. Use this instead:

SELECT count( DISTINCT userid )
FROM t1
WHERE STATUS = 'pending'

Upvotes: 5

Joachim Sauer
Joachim Sauer

Reputation: 308041

SELECT count( DISTINCT userid )
FROM t1
WHERE STATUS = 'pending'
GROUP BY userid

Upvotes: 0

konsolenfreddy
konsolenfreddy

Reputation: 9671

Do you want to count the number of user with status pending then?

SELECT count(userid)
FROM t1
WHERE STATUS = 'pending'
GROUP BY status, userid

Upvotes: 1

guillaumepotier
guillaumepotier

Reputation: 7448

Maybe adding DISTINCT() on userid?

Upvotes: 0

Réjôme
Réjôme

Reputation: 1484

Try to add the userid in the select clause :

SELECT userid, count( userid )
FROM t1
WHERE STATUS = 'pending'
GROUP BY userid

Upvotes: 0

Related Questions