I got a table "Product" and I want to calculate the number of Products which have the same price and assign this number to each product.
Table Product:
| Name | Price |
|---|---|
| A | 12 |
| B | 7 |
| C | 1 |
| D | 1 |
| E | 7 |
| F | 1 |
Expected Result:
| Name | Count |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| D | 3 |
| E | 2 |
| F | 3 |
CodePudding user response：
If your DBMS supports Window Functions, you may try the following simple query:
select name, count(*) over (partition by price) as P_Count
from Products order by name;
Otherwise, you may try the following:
select T.name, D.P_count
from Products T inner join
(
select price,count(price) as P_Count
from Products
group by price) D
on T.price=D.price
order by T.name;
See a demo from here.