Query with "count"?

[vickr1z]

Hi to all SQL guru..
im using Postgres _"command-line"_ i want to show you this query i create that will view the total counts of record of one particular area[city]...
SELECT count(municipality_code) FROM party_list_cocv INNER JOIN municipalities USING (municipality_code) INNER JOIN party_list_orgs ON party_id = party_list WHERE municipality_code = '980106000';
==which gives a result of:
count
-----
159
(1 row)

now what i want to do next is to view all of the records from party_list_cocv which has a records is less than or equal to 161. what should query i used to get a good result?
anyhelp will appreciate very much.
please ask me if this is not clear to you.. i reaally need help.. thnx.

 

[stoolpigeon]

If I understand your question, and I apologize if I don't. You can use a HAVING clause to find the items that fit your criteria for the count.

Since you are using count- you are going to have to group anything that comes back in the select.

So to get the count for everybody that meets your threshhold you could do something like this:

SELECT count(municipality_code) FROM party_list_cocv INNER JOIN municipalities USING (municipality_code) INNER JOIN party_list_orgs ON party_id = party_list group by municipality_code having count(municipality_code) <= 161;

Hope that helps

 

[vickr1z]

Hello atoolpigeon.
thanks for your help u really save my day..
the query really gives me good result and i added some fields
SELECT province_name, municipality_name, count(municipality_code) AS total_rec FROM party_list_cocv
INNER JOIN prrovinces USING (province_code)
INNER JOIN municipalities USING (municipality_code) INNER JOIN party_list_orgs ON party_id = party_list group by municipality_code having count(municipality_code) <= 161;

thanks again...