How can I get the count of a GROUP BY SQL query?
SELECT Voter.*, temp100.YadiNo, temp100.CountHouseNo FROM temp100 INNER JOIN Voter ON (temp100.HouseNo = Voter.HouseNo) AND (temp100.YadiNo = Voter.YadiNo) WHERE (((temp100.YadiNo) Between " & txtpartno.Text.Trim & " And " & txtpartno1.Text.Trim & ") AND ((temp100.CountHouseNo) Between " & txtfamilymeb.Text.Trim & " And " & txtfamilymeb1.Text.Trim & ")) ORDER BY voter.houseno
This means I has displayed group by house number. I want the count of this group of house numbers.
Advertisement
Answer
This is only a guess, this would count number of voters per House No., like:
SELECT Voter.houseno, COUNT(*) as CountHouseNo FROM temp100 INNER JOIN Voter ON (temp100.HouseNo = Voter.HouseNo) AND (temp100.YadiNo = Voter.YadiNo) WHERE (((temp100.YadiNo) Between " & txtpartno.Text.Trim & " And " & txtpartno1.Text.Trim & ") AND ((temp100.CountHouseNo) Between " & txtfamilymeb.Text.Trim & " And " & txtfamilymeb1.Text.Trim & ")) GROUP BY Voter.houseno ORDER BY Voter.houseno
And if you want to include the YadiNo
(whatever does this mean), you could do like this:
SELECT Voter.houseno, temp100.YadiNo, COUNT(*) as CountHouseNo FROM temp100 INNER JOIN Voter ON (temp100.HouseNo = Voter.HouseNo) AND (temp100.YadiNo = Voter.YadiNo) WHERE (((temp100.YadiNo) Between " & txtpartno.Text.Trim & " And " & txtpartno1.Text.Trim & ") AND ((temp100.CountHouseNo) Between " & txtfamilymeb.Text.Trim & " And " & txtfamilymeb1.Text.Trim & ")) GROUP BY Voter.houseno, temp100.YadiNo ORDER BY Voter.houseno
COUNT()
is an aggregate function that normally is paired with GROUP BY
clause.
See example here.