aspose file tools*
The moose likes JDBC and the fly likes Counting identical items in DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Counting identical items in DB" Watch "Counting identical items in DB" New topic
Author

Counting identical items in DB

Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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 ]

Never try to be a hard-worker. Be a smart-worker.
My Blog
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321



Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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

Joined: Aug 13, 2004
Posts: 1081

try making a query like below

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

Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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

Joined: Aug 13, 2004
Posts: 1081

What is your query ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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 ]

OCUP UML fundamental and ITIL foundation
youtube channel
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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

Joined: Oct 13, 2005
Posts: 39415
    
  28
Get the metadata from the ResultSet and go through the column names and types.
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

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 ]


Regards, Rene Larsen
Dropbox Invite
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Thanks all for your help and i try these to get the result in my JSP...
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

Joined: May 29, 2008
Posts: 445

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

Joined: Oct 13, 2005
Posts: 39415
    
  28
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

Joined: May 29, 2008
Posts: 445

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...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Counting identical items in DB