aspose file tools*
The moose likes JDBC and the fly likes How to count duplicate entries in one column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to count duplicate entries in one column" Watch "How to count duplicate entries in one column" New topic
Author

How to count duplicate entries in one column

Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
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


------------------8<------------------
please cut here
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8



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

Joined: Mar 02, 2010
Posts: 63
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
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8

"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

Joined: Oct 13, 2005
Posts: 36599
    
  16
I thought they were called twitchers
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8

Campbell Ritchie wrote:I thought they were called twitchers


Only the more... um... energetic ones.
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
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
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8



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

Joined: Mar 02, 2010
Posts: 63
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
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8

Actually "resultTwo.getInt(2)" is perfectly valid. You could (and should) check the API documentation to verify that.
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
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

Joined: Mar 02, 2010
Posts: 63
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

Joined: Mar 02, 2010
Posts: 63
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

Joined: Mar 02, 2010
Posts: 63
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
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8

Your countSpots query selects two columns. Which of them does your code at line 84 (just below that) read?
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
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
Bartender

Joined: Oct 14, 2005
Posts: 18165
    
    8

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

Joined: Mar 02, 2010
Posts: 63
Your right, exellent advice, thanks for your help and patience!

All the best wishes,

Patrick
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to count duplicate entries in one column
 
Similar Threads
Runtime Creating Tables
Display table rows using JSTL with different colors based on condition
Sequencing Records
How to hide columns in a JTable ?!!
how to change the order of a Linked hashMap