Counting identical items in DB

[Rajkumar balakrishnan]

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..

Product Name  CustomerName  Quantity
------------  ------------  --------
KeyboardKBX    Cust1           2
KeyboardXLS    Cust2           2

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]

SELECT COUNT(DISTINCT [Column Name])
FROM [Table Name]

[Rajkumar balakrishnan]

Originally posted by Muhammad Saifuddin:

SELECT COUNT(DISTINCT [Column Name])
FROM [Table Name]

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

SELECT * FROM tbl_rmadetails,tbl_customer,tbl_item WHERE tbl_rmadetails.customer_id=tbl_customer.customer_id AND tbl_rmadetails.item_id=tbl_item.item_id AND tbl_rmadetails.status='STS';

Thats a way it prints the output like this

Product Name CustomerName
------------ ------------
KeyboardKBX Cust1
KeyboardKBX Cust1
KeyboardXLS Cust2
KeyboardXLS Cust2

Then how could i implement the code that you given in this situation. Please help me.... :roll:

[Shailesh Chandra]

try making a query like below

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

Thanks,
Shailesh

[Rajkumar balakrishnan]

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]

What is your query ?

[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

select a, b, count(*)
from x, y -- your tables
where x.i = y.j -- your join criteria
group by a, b

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

[Rajkumar balakrishnan]

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

select a, b, count(*)
from x, y -- your tables
where x.i = y.j -- your join criteria
group by a, b

[ 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]

Get the metadata from the ResultSet and go through the column names and types.

[Rene Larsen]

If you add e.g. "as Number" to the count(*), then you can get it by rs.getInt("Number")

select a, b, count(*) as Number
from x, y -- your tables
where x.i = y.j -- your join criteria
group by a, b

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]

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

[Rajkumar balakrishnan]

Originally posted by Rene Larsen:
If you add e.g. "as Number" to the count(*), then you can get it by rs.getInt("Number")

select a, b, count(*) as Number
from x, y -- your tables
where x.i = y.j -- your join criteria
group by a, b

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

+----------------------------+-------------+----------+
| item_description           | customer_id | count(*) |
+----------------------------+-------------+----------+
| FARASOO KEYBOARD KBX78     | CI001       |        2 |
| FARASOO KEYBOARD KBX78     | CI002       |        1 |
| FARASOO KEYBOARD KBX78     | CI003       |        1 |
| FARASOO OPTICAL MOUSE REG  | CI001       |        1 |
| FARASOO OPTICAL MOUSE REG  | CI002       |        2 |
| FARASOO OPTICAL MOUSE REG  | CI003       |        1 |
| FARASOO OPTICAL MOUSE REG  | CI004       |        1 |
+----------------------------+-------------+----------+

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]

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 |
|----------------------------+-------------+----------+-----------+

I don't think standard SQL supports this requirement.

[Rajkumar balakrishnan]

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]

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]

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...