• 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

Counting identical items in DB

 
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 1325
Android Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is your query ?
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Get the metadata from the ResultSet and go through the column names and types.
 
Ranch Hand
Posts: 1179
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic