Skip to content
Advertisement

How to get the count of GROUP BY query?

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement