I already provide sql fiddle with schema and sample data.
http://sqlfiddle.com/#!2/e9d22/7/0
If I would like to know how many province and how many cities in Thailand.
Country Name | No. Provinces | No. Cities
Thailand | 77 | 1234
I guess that it need to use multiple COUNT(*) but I dont know how to use it.
Anybody know please suggest solution?
You need to use GROUP BY and COUNT:
SELECT c.name, count(distinct p.id) provincecoutn, count(distinct city.id) citycount
FROM country c
LEFT JOIN province p on c.id = p.country_id
LEFT JOIN City on p.id = city.province_id
GROUP BY c.name
Good luck.
Try this:
SELECT
C.Name, COUNT(DISTINCT P.Id) NoProvance, COUNT(CC.Id) NoCities
FROM country C
JOIN province P
ON C.Id = P.COUNTRY_ID
JOIN city CC
ON P.Id = CC.province_id
WHERE C.Name = 'Thailand'
GROUP BY C.Name
It's probably faster to count cities per province before joining to province:
SELECT c.name AS "Country Name"
,count(p.id) AS "No. Provinces"
,sum(ci.city_ct) AS "No. Cities"
FROM country c
LEFT JOIN province p ON p.country_id = c.id
LEFT JOIN (
SELECT province_id, count(*) AS city_ct FROM city GROUP BY 1
) ci ON ci.province_id = p.id
GROUP BY 1
-> sqlfiddle for PostgreSQL(!)
