Let's say I have following table "geo"

I would like to query following output (return distinct county province number of times country is seeing in the table)

so I can make it in 2 steps

1. query country and province

select country, province from geo
group by country, province

but I don't understand how can I go to desire result from step 1

is it possible to make it in one step?

CodePudding user response：

One generalized option is using the DISTINCT operator to aggregate on same provinces with respect to countries, then apply the COUNT window function with partial partitioning on countries.

WITH cte AS (
    SELECT DISTINCT Country, Province FROM tab
)
SELECT *, COUNT(Province) OVER(PARTITION BY Country) AS cnt
FROM cte

Here's a demo in MySQL, though this should be working on most DBMS'.

CodePudding user response：

You may prefer the simple (as per PM 77-1)

select country, province, count(*) As Kt
from geo
group by country, province