count number of same value column in data base using phpmysql

Question

i have the following table in phpmysql data base with three column , country , name and id(primary) show in html table one. it has multiple entry , i want to count the value and display it ( html table two.) what will be the mysql queries used for creating the second table

html table One

| id    | Country     | name |
|------:|------------:|-----:|
| 1     |USA          |   John
| 2     |USA          |   kAVI
| 3     | USA         |   Manoj
| 4     | USA         |   vijay
| 5     |Japan        |   Will
| 6     |Japan        |   Robert
| 7     |india        |   Vikas
| 8     |india        |   Monika
| 9     |india        |   Jecisa

html table two

|id   |Country|Count|
|----:|----..:|----:|
|1    |USA    |    4|
|2    |Japan  |    2|
|3    |india  |    3|

`Select min(id) as id, count(*) as cnt, country from tableOne group by country` — Bryan Dellinger, Jun 17 '22 at 17:40
The min(id) won't give you the right answer. You need to use row_number after group by. Here it is: with t as (select country ,count(*) as count ,min(id) as id from html_table_One group by country order by id) select row_number() over (order by id) as id ,country ,count from t — DannySlor, Jun 17 '22 at 22:56
Reopened. The `id` column adds here an element that does not appear in the post it was allegedly a duplicate of. — David דודו Markovitz, Jun 18 '22 at 12:52

score 0 · Answer 1 · answered Jun 18 '22 at 15:27

Here's a simple solution using group by to achieve the count and row_number to generate the id.

with 
t as 
(select  country
        ,count(*) as count
        ,min(id) as id
from     html_table_One 
group by country
order by id) 

select row_number() over (order by id) as id
      ,country
      ,count
from   t

id	country	count
1	USA	4
2	Japan	2
3	india	3

Fiddle

count number of same value column in data base using phpmysql

1 Answers1