Summing distinct rows

[mmlmitchell]

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

Thanks!

[smantscheff]

select sum(c.pop) from (SELECT pop FROM wi_allbcdata b WHERE b.countyname='Adams' GROUP BY blkidfp00) c;

[mmlmitchell]

Thanks for the reply.
Unfortunately it did not work.
I am trying to do this as a database query (using mysql_query) from a PHP script.

What I want do is sum the pop columns from each distinct row based on the blkidfp00 column.

I am trying to filter the results using a variable $county obtained from a $_GET command above the call in the script.

If so, my novice thinking is that the following query should work, but it doesn't:

$result=mysql_query"SELECT SUM(c.pop) FROM (SELECT DISTINCT blkidfp00 FROM wi_allbcdata WHERE countyname='$county' GROUP BY blkidfp00) c";

I really appreciate your help on this.

[smantscheff]

I do not understand. What does not work? My query returns the sum of all pop values for each single blkidfp00 value - which is 2333 in your example.
Which result do you expect from this test case? Replace 'Adams' by your '$county' $_GET variable, and there you are. Or aren't you?

drop table if exists wi_allbcdata;
create table wi_allbcdata
(id integer,
blkidfp00 char(30),
countyname char(30),
servicetype integer,
pop integer
);
insert into wi_allbcdata values 
('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');

SELECT blkidfp00, pop FROM wi_allbcdata b WHERE b.countyname='Adams' GROUP BY blkidfp00;
+----------------+------+
| blkidfp00      | pop  |
+----------------+------+
| 55001950100100 | 1980 |
| 55001950100101 |  353 |
+----------------+------+
select sum(c.pop) from (SELECT pop FROM wi_allbcdata b WHERE b.countyname='Adams' GROUP BY blkidfp00) c;
+------------+
| sum(c.pop) |
+------------+
| 2333       |
+------------+

[mmlmitchell]

smantscheff,
Many thanks for your help.
Your solution DOES work - There was another problem with my script that was causing trouble. Now, on to the next problem...

[smantscheff]

Before you move on to the next problem, mark this thread as solved.