SQL with aggregate function with two rows-CodePudding

I have the following tables:

and I'm working on a query that returns the number of voters (the idcard number), who have voted in the municipality m1. As can be seen, the municipality n1 has two districts: 1 and 2, which have different voters. This is my code:

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

But I have the problem that the subquery returns two rows, so the count cannot be done. Could someone enlighten me?

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);