?
Solved

MySQL Count

Posted on 2012-08-24
8
Medium Priority
?
347 Views
Last Modified: 2012-09-09
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
0
Comment
Question by:Gary
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38331643
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

0
 
LVL 58

Accepted Solution

by:
Gary earned 0 total points
ID: 38331650
No that wouldn't work, but I have got it in the meantime

SELECT *, (select count(id) from table1 b where
a.id = b.id and b.userid='123' ) as idcount
FROM table1 a

WHERE datetime= '2012-08-24'
AND userid='123'
ORDER BY datetime DESC;
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38331660
OK, I misunderstood ... I thought you were just doing an existence check and not after all of the row data as well.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 58

Author Comment

by:Gary
ID: 38331664
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38331677
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.
0
 
LVL 58

Author Comment

by:Gary
ID: 38332491
Each client has users, I  select all clients and then need a count on all users individualy per client..
0
 
LVL 41

Expert Comment

by:Sharath
ID: 38365809
If you are still looking for help, please provide some sample input data and the expected result.
0
 
LVL 58

Author Closing Comment

by:Gary
ID: 38380602
Worked it out myself
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question