File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Determining how many records have same field Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Determining how many records have same field" Watch "Determining how many records have same field" New topic
Author

Determining how many records have same field

Luke Shannon
Ranch Hand

Joined: Sep 30, 2004
Posts: 239
I am trying to get a count of how many records in a table have the same date field.

I want to do something like this:

SELECT count(*) from auction where contactID = 360 and date = date;

Problem is the above gets all records.

Is this something I need to do programatically in Java or can this be done in SQL?

Thanks,

Luke


Luke
SCJP
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Luke,

I may be misunderstanding what you are asking but I think the answer is group by.



Notice the field name auctiondate because I know you do not have a field name that is a SQL reserved word like date which will cause you many a headache.

Try that and see what it does... namely does it help?
Luke Shannon
Ranch Hand

Joined: Sep 30, 2004
Posts: 239
I think this may be it:

SELECT count(*), date from auction where contactID = 360 GROUP BY date LIMIT 1;

Basically I want to know if contactID 360 contains any auction records with the same date value.

Your original call gives me a count per date, with the larger count numbers appearing at the top of the call.

If I limit by one and get back a count great than 1, then the case is true. This contactID does have a date with more than one auction occuring in it.

Does this make sense?

Luke
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Yes your logic appears correct to me. The only concern is that you indicate that it orders it by count for you, which I am a bit surprised at. Usually I find I have to tell it to order the set by the count to get it to do that.

This is important because you are relying on the max count to be at the top.

I have a feeling that you are using MySQL so the following should work

SELECT count(*) AS datecount, date from auction where contactID = 360 GROUP BY date ORDER BY datecount DESC LIMIT 1;

The aliasing is needed so that you can reference the column that is a function by the order by clause.

Anyway that query will work with the logic you suggested and wouldn't fall victim to any arbitrary sorting.
Luke Shannon
Ranch Hand

Joined: Sep 30, 2004
Posts: 239
Thank you. Things are working smooth now.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Great. If it is at all possible I renew my suggestion to rename that field from date to something else. SQL reserved words should never be used as identifiers and if it hasn't caused you problems yet it most likely will sooner than you like.
Luke Shannon
Ranch Hand

Joined: Sep 30, 2004
Posts: 239
Good point. Thanks again.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Determining how many records have same field