Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Determining how many records have same field

 
Luke Shannon
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. Things are working smooth now.
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good point. Thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic