Similar to the issue in http://www.daniweb.com/forums/thread227151.html
I have multiple rows with identical values; in this case they are Census Blocks (CB --> blkidfp00) that have 15 digit values. I have multiple rows because each is differentiated by the type of services available in each CB, and there are many different types of service types available in each CB. On each row, I have the population (--> pop) for that CB. So, several rows with the same CB have have unique service codes, but the same population values. There are multiple CBs in a county (--> countyname).
What I want to do is sum all the population values for all the CBs in a given county.
I have studied several sources and it seems the line below should work, but it doesn't. Note, I can't change the structure of the database. Below is an example of the database values from which I am trying to extract.
id | blkidfp00 | countyname | servicetype | pop
1 | 55001950100100 | Adams | 10 | 1980
2 | 55001950100100 | Adams | 20 | 1980
3 | 55001950100100 | Adams | 30 | 1980
4 | 55001950100101 | Adams | 10 | 353
5 | 55001950100101 | Adams | 20 | 353
6 | 55001950100101 | Adams | 30 | 353
Here is the mysql_query
$sumquery="SELECT *, SUM(pop) AS total_pop FROM (SELECT DISTINCT blkidfp00 FROM wi_allbcdata WHERE countyname='$county') GROUP BY total_pop";
The result I seek is population = 2333