Count in query

Databases
1

I am using the query below:

SELECT * FROM history WHERE user_id=1 AND (status=‘Transferred’ OR status=‘Sale’) AND dates LIKE(‘2014-09%’) group by reference order by dates desc

It returns 1 row which is fine as I have used group by but when I try to check the number of records I am getting 3 with below query:

SELECT count(*) as counter FROM history WHERE user_id=1 AND (status=‘Transferred’ OR status=‘Sale’) AND dates LIKE(‘2014-09%’) group by reference order by dates desc

How can I get 1 in the counter as first query returns one record.

2

COUNT(*) counts the number of records found within your grouped result set - this is often a desirable behaviour. Use COUNT DISTINCT, try this:

SELECT count(DISTINCT reference) as counter FROM history WHERE user_id=1 AND 
(status='Transferred' OR status='Sale') AND dates LIKE('2014-09%')

In this case you don’t need GROUP BY nor ORDER BY for counting.

3

It worked great thanks

4