• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to count duplicate entries in one column

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I thought they were called twitchers
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your right, exellent advice, thanks for your help and patience!

All the best wishes,

Patrick
 
She'll be back. I'm just gonna wait here. With this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic