Jump to content

Archived

This topic is now archived and is closed to further replies.

Nomax5

Help Totalling up table records ** SOLVED **

Recommended Posts

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?



Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
I'm still having problems trying to get this working

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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()

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.