Jump to content


Photo

Help Totalling up table records ** SOLVED **


  • Please log in to reply
10 replies to this topic

#1 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 25 July 2006 - 12:20 PM

Hello,

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

If I have a database table that stores the following 

Name  Desc          example 
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?





#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 25 July 2006 - 12:30 PM

Not sure but you could try

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

Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 26 July 2006 - 03:21 PM

I'm still having problems trying to get this working

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 26 July 2006 - 03:42 PM

have you tried the query posted by king arthur?
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 26 July 2006 - 04:06 PM

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?


#6 thepip3r

thepip3r
  • Members
  • PipPipPip
  • Advanced Member
  • 289 posts

Posted 26 July 2006 - 04:11 PM

see this line here? 

SELECT car, cnt, gen, age, kids, count(*) as total

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

#7 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 26 July 2006 - 04:21 PM

I get an ERROR in SELECT with that statement

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

$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 . "]";


#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 26 July 2006 - 04:31 PM

$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'];
}

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#9 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 26 July 2006 - 04:50 PM

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.


#10 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 26 July 2006 - 05:03 PM

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()
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#11 Nomax5

Nomax5
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationYork England

Posted 26 July 2006 - 05:26 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users