Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Counting identical items in DB

 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to count the identical items present in the DB. I am using MySQL.

This is what i got as a result of a MySQL query..
Product Name CustomerName
------------ ------------
KeyboardKBX Cust1
KeyboardKBX Cust1
KeyboardXLS Cust2
KeyboardXLS Cust2

But i want this to be print like this..


Please let me know the way to accomplish the task. It would be helpful if a piece of code is provided.Thanks in advance....
[ August 23, 2008: Message edited by: Rajkumar balakrishnan ]
 
Muhammad Saifuddin
Ranch Hand
Posts: 1324
Android Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Muhammad Saifuddin:


OK.... Here is the query that i used to obtain the result that i mentioned in the first post.


Thats a way it prints the output like this



Then how could i implement the code that you given in this situation. Please help me.... :roll:
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try making a query like below

Select ProductName, CustomerName, Count(CustomerName) from table_name group by ProductName,CustomerName

Thanks,
Shailesh
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Shailesh Chandra:
try making a query like below

Select ProductName, CustomerName, Count(CustomerName) from table_name group by ProductName,CustomerName

Thanks,
Shailesh


Not working Shailesh.. If you find some good links for MySQL then please give me that.. I was tired of searching one using google....
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is your query ?
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the logic you can follow:
select the rows where you want to create the count for,
use the group by clause to combine identical values
use count(*) to count the number of combine records


[ August 27, 2008: Message edited by: Jan Cumps ]
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jan Cumps:
This is the logic you can follow:
select the rows where you want to create the count for,
use the group by clause to combine identical values
use count(*) to count the number of combine records



[ August 27, 2008: Message edited by: Jan Cumps ]


How could i get the count coloumn in JSP... may i use like this..
rs.getString("count");???
Is this rite?

[ August 28, 2008: Message edited by: Rajkumar balakrishnan ]
 
Campbell Ritchie
Sheriff
Posts: 48453
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Get the metadata from the ResultSet and go through the column names and types.
 
Rene Larsen
Ranch Hand
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you add e.g. "as Number" to the count(*), then you can get it by rs.getInt("Number")



Originally posted by Rajkumar balakrishnan:


How could i get the count coloumn in JSP... may i use like this..
rs.getString("count");???
Is this rite?


[ August 28, 2008: Message edited by: Rajkumar balakrishnan ]
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks all for your help and i try these to get the result in my JSP...
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Rene Larsen:
If you add e.g. "as Number" to the count(*), then you can get it by rs.getInt("Number")





I got the perfect output as like the below...


But at the same time if the count is 2 means i need those two products serial number like this..

+----------------------------+-------------+----------+-----------+
| item_description | customer_id | count(*) | serial_no |
+----------------------------+-------------+----------+-----------+
| FARASOO KEYBOARD KBX78 | CI001 | 2 | MO77,MO56 |
|----------------------------+-------------+----------+-----------+


How could i get output like this... Please help me ranchers....
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But at the same time if the count is 2 means i need those two products serial number like this..
I don't think standard SQL supports this requirement.
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jan Cumps:
I don't think standard SQL supports this requirement.


Are you sure with what you said....OK....then i must google it..
 
Campbell Ritchie
Sheriff
Posts: 48453
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jan Cumps:
I don't think standard SQL supports this requirement.
You asked about counting identical items, and you have now told us the items are no longer identical . . .
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Campbell Ritchie:
You asked about counting identical items, and you have now told us the items are no longer identical . . .


NO... i said the item description field is identical and each item description has a serial number and i want to display those serial numbers in single field as i mention above...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic