Sql New Function-CodePudding

New question updated on main page

CodePudding user response：

that depends if ndistrict are identical or different

identical

SELECT COUNT(idcard) FROM Census
WHERE ndistrict = (SELECT DISTINCT ndistrict FROM Polling WHERE name='m1')

If there are multiple ndistrict use

SELECT COUNT(idcard) FROM Census
WHERE ndistrict IN (SELECT DISTINCT ndistrict FROM Polling WHERE name='m1')

CodePudding user response：

SELECT count(*) AS votes
FROM  (SELECT ndistrict FROM polling WHERE name = 'm1') p
JOIN   census USING (ndistrict);

Works for any number of rows returned from subquery p.
It assumes no duplicates from that subquery (given since ndistrict is the PK of polling according to your table definition), and idcard to be defined NOT NULL (so that the faster count(*) is equivalent to count(idcard).

It returns the number of votes, which is not necessarily the same as:

the number of voters (the idcard number), who have voted in the municipality 'm1'

If the same voter can have cast multiple votes, to count distinct voters:

SELECT count(DISTINCT idcard) AS voters
FROM  (SELECT ndistrict FROM polling WHERE name = 'm1') p
JOIN   census USING (ndistrict);