asked on

MySQL Count

I'm doing a select based on user id and date but want to check if the user id exists already in the table or is new. So something like this

select * from table
WHERE datetime= '2012-08-25'
AND client='123'

But I need to add in a total count for the userid (in the same table) for any date

Lee Wadwell

How about:

select max(case when datetime= '2012-08-25' then 1 else 0) as datematch,
       count(*)
from table 
WHERE client='123'

Open in new window

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee Wadwell

OK, I misunderstood ... I thought you were just doing an existence check and not after all of the row data as well.

Gary

ASKER

No, I need to get all records for the date selected but also check if any of the id's in that select already exist in the table i.e. its not a one off record with that id under that clients id.
Unless you can see a better way without the nested select.

Lee Wadwell

No ... I think you have nailed it. Although I am not sure I fully understand the difference between userid and id (client id?).

You could do ...

SELECT *
FROM table1 a
JOIN (select id, count(id) as idcount 
      from table1 
      where b.userid='123' group by id) b ON a.id = b.id
WHERE datetime= '2012-08-24' 
AND userid='123'
ORDER BY datetime DESC;

Open in new window

but I do not think that it is better. Each would perform slightly differently ... try both and see if you notice a difference, but without large tables - I doubt it.

Gary

ASKER

Each client has users, I select all clients and then need a count on all users individualy per client..

Sharath S

If you are still looking for help, please provide some sample input data and the expected result.

Gary

ASKER

Worked it out myself