Sum Values from Rows that meet multiple parameters

[mmlmitchell]

Hello,

Building on the thread: http://www.daniweb.com/forums/post1491690.html#post1491690

I need to tease out the sums of three values from Rows that meet multiple parameters.

Here is a sample of the dataset:

id | blkidfp00 | fips | transtech | pop | hu | busfirms
1 | 55001950100100 | 550039 | 10 | 1980 | 402 | 39
2 | 55001950100100 | 550039 | 20 | 1980 | 402 | 39
3 | 55001950100100 | 550039 | 30 | 1980 | 402 | 39
4 | 55001950100101 | 550039 | 10 | 3867 | 642 | 93
5 | 55001950100101 | 550039 | 20 | 3867 | 642 | 93
6 | 55001950100101 | 550039 | 30 | 3867 | 642 | 93
7 | 55001950100102 | 550039 | 20 | 9672 | 763 | 85
8 | 55001950100102 | 550039 | 30 | 9672 | 763 | 85
9 | 55001950100102 | 550039 | 40 | 9672 | 763 | 85
10 | 55001950100103 | 550039 | 20 | 8336 | 844 | 71
11 | 55001950100103 | 550039 | 50 | 8336 | 844 | 71
12 | 55001950100103 | 550039 | 60 | 8336 | 844 | 71

I need to sum the pop, hu and busfirms (as 3 distinct values) for a fips where transtech equals 10 and 30.

So, here is the query I created that does not work as it should:

$result=mysql_query("SELECT SUM(f.pop), SUM(f.hu), SUM(f.busfirms) 
			FROM (SELECT pop, hu, busfirms FROM wi_allbcdata g 
			WHERE g.fips='$countyfips' 
			AND g.transtech=10 
			OR g.transtech=30 
			GROUP BY transtech, blkidfp00) f");

The result I seek is:
SUM(f.pop) = 15,519
SUM(f.hu) = 1,807
SUM(f.busfirms) = 217

In other words, I want the sums of pop, hu and busfirms where blkidfp00 for distinct blkidfp00 where trantech = 10 or 30.

Kinda complex, but certainly not rocket science (I hope).

[smantscheff]

If you submit your data in the form of a test case - that is as MySQL statements which can directly entered into the mysql command line interface reproducing your problem, as in my post http://www.daniweb.com/forums/post1492091.html#post1492091 - I'm willing to give it a try.

[urtrivedi]

<?php

$result=mysql_query("SELECT transtech, blkidfp00,SUM(g.pop) popsum, SUM(g.hu) sumhu, SUM(g.busfirms) sumbusfirms FROM wi_allbcdata g 
			WHERE g.fips='$countyfips' 
			AND (g.transtech=10 OR g.transtech=30 )
			GROUP BY transtech, blkidfp00");
?>