Page 1 of 1

Summary of Totals

Posted: Wed Mar 01, 2006 8:09 am
by BigJonMX
Hello Everyone,
i am always having trouble with totals...
$q="SELECT a,b,c,d FROM table" - ok, this is simplified (original = ...FROM Ta,Tb,Tc,Td WHERE lots....)
And i get the results i need...
a | b | c | d
solid | y | y | blue
solid | y | y | red
solid | y | y | red
solid | y | n | blue
solid | y | n | blue
solid | n | y | blue
solid | y | y | blue
nots | y | y | blue
nots | y | y | blue
...basically every combination, sometimes one result, sometimes many...

And i want a summary...
a | b | c | d | total
solid | y | y | blue | 1
solid | y | y | red | 2
solid | y | n | blue | 2
solid | n | y | blue | 1
solid | y | y | blue | 1
nots | y | y | blue | 2

i've almost got it working by looping through the records and keeping a running-total for each unique record
but its very messy.

Anyone know a better way (Eg - just in an sql)???

Thnaks, J

Almost there

Posted: Wed Mar 01, 2006 8:22 am
by BigJonMX
i can make a summary table real quick, like this...
$q="SELECT DISTINCT a,b,c,d FROM Ta,Tb WHERE Ta_id=Tb_id ORDER BY a,b,c,d"

But i still dont have any values...

i am tempted to do a query inside a loop (not a good idea, eh?!)

echo "a b c d SUM";
while ($row=mysql_fetch_array($r)): extract($row);
$q2="SELECT COUNT(*) AS total WHERE a=$a, b=$b, c=$c, d=$d FROM..."
echo $a $b $c $d $total
endwhile

Any improvements ????
J

Posted: Wed Mar 01, 2006 9:22 am
by feyd
a GROUP BY clause might work..