• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to count duplicate entries in one column

 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to write a statement that counts duplicate entries in a column in a MySQL database called `spot`.

Column1 gets his entries straight from the database and numberOfDuplicates counts how many times how many duplicates of that entry in Column1 exist in the database.


Can anybody help me with this? I've searched the internet for quite a few days now, but I can't find anything that will work for me.

Thanks for your time and effort, it's most appreciated!

Patrick

 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


That will only give you this:


but then I can't imagine why you would want what you posted. Could you explain that requirement?
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to write a program for my school. It's an app for bird-watching-enthusiasts (don't know how you call them in English, we call them 'vogelaars').

The idea is that people who spot a specific bird-type, can add the name of that bird to the database. Furthermore the program needs to count how many birds of that type were spotted in total by the community. So the program needs to scan how many times that specific birdname, ie magpy, was entered in the column species.

After that I can try to display the statement with al the properties of a specific birdspot and the statement for the bird count in one JList row.

Hope I made it a little clearer, thanks for your time!
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Birders". And we already have eBird... however, yes, I know, you're writing this program for education.

So do you want a table like this:

or do you want one like this:


Or do you want something else? In any case you're probably going to need a Group By clause as in my original response.
 
Campbell Ritchie
Sheriff
Pie
Posts: 48940
60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought they were called twitchers
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Campbell Ritchie wrote:I thought they were called twitchers


Only the more... um... energetic ones.
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much, but I'm mostly interested in the the 'count column'. I'm able to display al the other columns in a JList just fine, but can't get the 'count column' working. Here is a part of my code, I think there is a problem with the count string and vRow.addElement(resultTwo.getString(1)); When I replace your `count` statement with a copy of the `selectAllSpots`, the program does compile, I don't know why.

 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


The column named in your Group By clause must also be one of the columns in your Select clause. (Or did you translate "soort" into "species" for the benefit of us anglophones?)
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You got me there I went to fast with posting my translated code, I'm sorry for the confusion, but `soort` instead of `species` os not the problem in my code.

How can I display the statement count = "SELECT species, count(*) from table group by species"; in a JList?

I have this feeling that I have to use different code than for a statement like selectAllSpots = "SELECT `birder`, `species`, `date`, `time` FROM spot;"; Because the selectAllSpots statement is made from already existing fields in a database. The count statement is only a query on that database, there is no existing field called count, so I can't iterate trough the count statement like I can in the selectAllSpots statement, and resultTwo.getString(1)); doesn't make sense iether I guess.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually "resultTwo.getInt(2)" is perfectly valid. You could (and should) check the API documentation to verify that.
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been searching the forum/net and changing my code for like 5 hours right now, but keep getting this error: java.lang.Exception: SQL foutmelding You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table group by soort' at line 1

I have changed my Vector into an arrayList, because I couldn't lRow .add(resultTwo.getint()); working with a vector, not that it helped, but I think lists are better anyway because I'm reading everywhere that Vectors have been deprecated since 1887. (Why does my teacher want me to use Vectors?).

This is the code I have so far, everything is working for resultOne, problems start when I want to add resultTwo at the end of every new row. Any help would be much appreciated, I had two days off to work on this assignment, I have learned a lot, but the code is pretty much still the same as it was at the beginning of this week

 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have changed to And now I get different errors , but not about the MySQL part of my code, Yay!
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It does count the duplicate rows in the column `soort`and displays it in the Jlist, but only when there is only one of the same `soort`, (so no duplicate a all), it doesn't compile when I have 2 magpies in the column `soort`.

So if I have 1 Magpie my JList gets populated with data from resultOne and resultTwo, but when I add a row with another magpie it says "SQL error After end of result set".




 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got a little further, but still problems with count statement. The statement displays the name of the bird and not how many times it was found in the column.

Here is my frontend:



Here is my core code:



And here is my SQL Connector

 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your countSpots query selects two columns. Which of them does your code at line 84 (just below that) read?
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok I think I think I understand: I thought the countSpots query only gave back an int for the counted rows, but it also shows a column for 'soort' (SELECT 'soort') am I right?

So if I'm right it is also possible to make a combination of the two statements? i.e.: "SELECT COUNT(*) FROM `spot` GROUP BY `soort`, `spotter`, `soort`, `datum`, `tijd` FROM `spot`";
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For the first question: Yes.

For the second question: I think it's time you stopped programming SQL by random guessing. Go and get an SQL reference and learn a bit about the language. You can
run MySQL from the command line and try queries, so start doing that.
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your right, exellent advice, thanks for your help and patience!

All the best wishes,

Patrick
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic