Help Totalling up table records ** SOLVED **

[Nomax5]

Hello,

I wonder could someone give me some pointers with this problem please.

If I have a database table that stores the following 

[u][b]Name  Desc          example  [/b] [/u]
CAR   Car model       Ford BMW etc.
CNT   country code  US UK etc
GEN   gender          M or F
AGE   age range      1 to 6
KID   Kids              Y or N

I want to show in descending percentage order
The most popular demographic for a particular car like this:

BMW results
US Male 30’s No kids    29%
UK Male 40’s Kids        21%
US Female 20’s no kids 19%
US Male 50’s kids         13%
DE Male 30’s no kids       8%
US female 60’s kids        6%
DE Male 30’s no kids      4%

It doesn’t have to be a percentage it can just be a number count.

I know I can select * where CAR = “BMW” and get all the records
I know there are 24 combinations of gender age and kids so I
Could do a select block for that.
But there are 200+ countries

What is the most efficient method of doing stuff like this?

[king arthur]

Not sure but you could try

[code]
SELECT car, cnt, gen, age, kids, count(*) as total FROM tablename GROUP BY car, cnt, gen, age, kids ORDER BY total DESC
[/code]

[Nomax5]

I'm still having problems trying to get this working

[ryanlwh]

have you tried the query posted by king arthur?

[Nomax5]

yes I have king arthur example working I think

But How do I access the total for a particualar group? 

suppose I had 10 records for a certain car say BMW
and on 3 of those records were exactly the same  then that would constitute 30%
it's only where records are exactly the same I'm not interested in
how many US females drive BMW's
or even US females with kids drive BMW's
only when all the variables are the same  US females 30's kids

so in the above example at some point that "total" in the select statement = 3  how do I define that in php

echo total; or result(total) or something?

[thepip3r]

see this line here? 

[code]SELECT car, cnt, gen, age, kids, count(*) as total[/code]

after running this query... echo $total and see what you get.

[Nomax5]

I get an ERROR in SELECT with that statement

The field names are different but this is what I've got

[code]
$sql = "SELECT gak_ctry, gak_gend, gak_age, gak_kids, count(*) as total";
$result = mysql_query($sql,$connection) or die ("ERROR in $sql");
$row = mysql_fetch_array($result);
echo "[" . $total . "]";
[/code]

[ryanlwh]

[code]$sql = "SELECT gak_car, gak_ctry, gak_gend, gak_age, gak_kids, count(*) as total FROM gak GROUP BY gak_car,gak_ctry,gak_gend,gak_age,gak_kids ORDER BY total DESC";
$result = mysql_query($sql,$connection) or die ("ERROR in $sql");
$alltotal = mysql_num_rows($result);
while($row = mysql_fetch_assoc($result))
{
  if($prev != $row['gak_car']) echo '<br>'.$row['gak_car'].' results<br>';
  echo $row['gak_ctry'].' '.$row['gak_gend'].' '.$row['gak_age'].' '.$row['gak_kids'].' '.(($row['total']/$alltotal)*100).'%<br>' ;
  $prev = $row['gak_car'];
}[/code]

[Nomax5]

wow how do you guys do it?

I mean how do you work?

This is what I do
I have my php editor on my PC,
I make a change (small)
flip to ftp drag n drop it
then go to the website to see if it works

I get very few error messages - nothing happens if it fails usually

then I rinse and repeat

do you have some sort of interactive sql thing going on?

also a little off topic how do I round off the percentages please?
I've got 22.222222222222% etc.

[ryanlwh]

i worked this problem out of my head... didn't test it, but experience got me correct.

i use dreamweaver at home and write directly on the server at work... for more complex stuff i need to run it. but for simple stuff i knew it would work right off my head.

to check sql queries easily, you can download phpmyadmin or the mysql query browser from mysql. and the mysql command line that comes with the installation is also a good tool.

for rounding off, try number_format(), or a simpler round()

[Nomax5]

Ahh I see,  in your head ehh
so to summarize: basically I’m as thick as the bar charts I’m about to make  lol.  :o

No…no… it’s okay I can take it on the chins

Thank you so much for your help I’m off and running now, Happy as a pig in poo ;D

I’ll change the heading on this to Solved

thanks.