Summary of Totals

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

Summary of Totals

Post 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
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

Almost there

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

a GROUP BY clause might work..
Post Reply