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).