PHP SQL - Calculations in MySQL query

Author

Calculations in MySQL query

Ja NE

2005-11-18, 7:56 am

I would like to get some statistic data about my members. I have column
named YYYY with int(4) where some members have entered their year of
birth. Now, I would like to see how many of them are, for example, age
40. So I have to serach for year 1965.:
(SELECT COUNT(*) FROM mytable WHERE YYYY="1965")
but douing so, I will need to make querys form 1940 (my oldest member)
to 1991 (my youngest member). and that is a lot of "questions"...

Is there any way to made such query "simplier", i.e. faster?

something like... don't know, missing knowlage about mysql possibilites
need a sort of "array like query"...

anyone have any idea? (I haven't found mysql refernce manual of big
help. isn't that good as php on-line manual)

tnx in advance.

--
Ja NE
http://fotozine.org/?omen=janimir
--

J.O. Aho

2005-11-18, 7:56 am

Ja NE wrote:
> I would like to get some statistic data about my members. I have column
> named YYYY with int(4) where some members have entered their year of
> birth. Now, I would like to see how many of them are, for example, age
> 40. So I have to serach for year 1965.:
> (SELECT COUNT(*) FROM mytable WHERE YYYY="1965")
> but douing so, I will need to make querys form 1940 (my oldest member)
> to 1991 (my youngest member). and that is a lot of "questions"...

SELECT YYYY, COUNT(*) FROM mytable GROUP BY YYYY

This way you will get a list looking something like:

1940 1
1943 2
1947 6
1948 3
....

> something like... don't know, missing knowlage about mysql possibilites
> need a sort of "array like query"...

$user_per_year=array(array("Year","# Users");
$result=mysql_query("SELECT YYYY, COUNT(*) FROM mytable GROUP BY YYYY");
while(row=mysql_fetch_array($result)) {
array_push($user_per_year,array($row[0],
$row[1]));
}
print_r($user_per_year);

//Aho

Ja NE

2005-11-18, 7:56 am

J.O. Aho <user@example.net> wrote:

> SELECT YYYY, COUNT(*) FROM mytable GROUP BY YYYY

tnx. that's it. :)

--
Ja NE
http://fotozine.org/?omen=janimir
--

Sponsored Links