mysql help needed

**Paul Lautman** · Jul 6 '06, 07:55 AM

Re: mysql help needed

Bob Bedford wrote:

Hi all,
>
I've ever the same problem.
>
Table1
idperson, name, zip
>
table2
zip, city, region.
>
Table2: same zip, many cities (1000, Lausanne - 1000, Lausanne1, ...)
>
Now I'd like ONE query wich return the name, zip and city, but just
one record per zip.
>
I mean if a record in table1 has zip 1000, I only want the first
occurence of the city (in my case Lausanne).
Actually I get as many records as they are cities with the same zip.
>
namex,1000,Laus anne
namex,1000,Laus anne1
namex,1000,Laus anneN....
>
How to do so ? in fact what I need is count the number of peoples in a
region, and the region is linked to a zip.
>
1000, Lausanne, VD
1000, Lausanne1, VD....
>
the actual query is
select count(idperson) , region from table1 inner join table2 on
table1.zip = table2.zip group by region.
Please help.
>
Bob

Might I suggest that this might be better asked in comp.databases. mysql

**Bob Bedford** · Jul 6 '06, 08:15 AM

Re: mysql help needed

Hi Paul,

Might I suggest that this might be better asked in comp.databases. mysql

Unfortunately, I don't have access to mysql newsgroup, that's why I ask
here, as many php programmers are also mysql addicts...

Bob

**Paul Lautman** · Jul 6 '06, 08:55 AM

Re: mysql help needed

How to do so ? in fact what I need is count the number of peoples in a

region, and the region is linked to a zip.
>
1000, Lausanne, VD
1000, Lausanne1, VD....

Is there a many to one relationship between region and zip thus:

1000, Lausanne, VD
1000, Lausanne1, VD
1003, Somewhere, VD

**strawberry** · Jul 6 '06, 08:55 AM

Re: mysql help needed

untested...!

select distinct zip,region,coun t(idperson)
from table1
left join table2 on table2.zip = table 1.zip
group by zip

Bob Bedford wrote:

Hi Paul,
>

Might I suggest that this might be better asked in comp.databases. mysql

>
Unfortunately, I don't have access to mysql newsgroup, that's why I ask
here, as many php programmers are also mysql addicts...
>
Bob

**Paul Lautman** · Jul 6 '06, 09:05 AM

Re: mysql help needed

Bob Bedford wrote:

Hi all,
>
I've ever the same problem.
>
Table1
idperson, name, zip
>
table2
zip, city, region.
>
Table2: same zip, many cities (1000, Lausanne - 1000, Lausanne1, ...)
>
Now I'd like ONE query wich return the name, zip and city, but just
one record per zip.
>
I mean if a record in table1 has zip 1000, I only want the first
occurence of the city (in my case Lausanne).
Actually I get as many records as they are cities with the same zip.
>
namex,1000,Laus anne
namex,1000,Laus anne1
namex,1000,Laus anneN....
>
How to do so ? in fact what I need is count the number of peoples in a
region, and the region is linked to a zip.
>
1000, Lausanne, VD
1000, Lausanne1, VD....
>
the actual query is
select count(idperson) , region from table1 inner join table2 on
table1.zip = table2.zip group by region.
Please help.
>
Bob

Would you be looking for this:

SELECT count( DISTINCT a.idperson ) , b.region
FROM `table1` a
JOIN `table2` b
USING ( zip )
GROUP BY region

**Paul Lautman** · Jul 6 '06, 09:05 AM

Re: mysql help needed

strawberry wrote:

untested...!
>
select distinct zip,region,coun t(idperson)
from table1
left join table2 on table2.zip = table 1.zip
group by zip
>

Please try not to top post.

I don't think that quite does it. Check my other posts.

**Bob Bedford** · Jul 6 '06, 10:05 AM

Re: mysql help needed

"Paul Lautman" <paul.lautman@b tinternet.coma écrit dans le message de
news: 4h425mF1prj87U1 @individual.net...

Bob Bedford wrote:

>Hi all,
>>
>I've ever the same problem.
>>
>Table1
>idperson, name, zip
>>
>table2
>zip, city, region.
>>
>Table2: same zip, many cities (1000, Lausanne - 1000, Lausanne1, ...)
>>
>Now I'd like ONE query wich return the name, zip and city, but just
>one record per zip.
>>
>I mean if a record in table1 has zip 1000, I only want the first
>occurence of the city (in my case Lausanne).
>Actually I get as many records as they are cities with the same zip.
>>
>namex,1000,Lau sanne
>namex,1000,Lau sanne1
>namex,1000,Lau sanneN....
>>
>How to do so ? in fact what I need is count the number of peoples in a
>region, and the region is linked to a zip.
>>
>1000, Lausanne, VD
>1000, Lausanne1, VD....
>>
>the actual query is
>select count(idperson) , region from table1 inner join table2 on
>table1.zip = table2.zip group by region.
>Please help.
>>
>Bob

>
Would you be looking for this:
>
SELECT count( DISTINCT a.idperson ) , b.region
FROM `table1` a
JOIN `table2` b
USING ( zip )
GROUP BY region

Hi Paul,

exactly !!! thanks !

Why it does seem so simple once the answer is known ??? Didn't think to put
the distinct inside the ()...

Great, thanks again for your help.

**Jerry Stuckle** · Jul 6 '06, 10:15 AM

Re: mysql help needed

Bob Bedford wrote:

Hi Paul,
>
>

>>Might I suggest that this might be better asked in comp.databases. mysql

>
>
Unfortunately, I don't have access to mysql newsgroup, that's why I ask
here, as many php programmers are also mysql addicts...
>
Bob
>
>
>

You can access it through Google Groups. And ask your usenet provider
to start carrying it. I've found most are quite amiable if you ask nicely.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

**Bob Bedford** · Jul 6 '06, 03:25 PM

Re: mysql help needed

Hi all,

SELECT count( DISTINCT a.idperson ) , b.region
FROM `table1` a
JOIN `table2` b
USING ( zip )
GROUP BY region

Now I've an other question, as I can't get it to work, even with your
example.

I've a table where there is a datetime field.

I'd like to count the number of articles created every day. How to do so ?

I've tried this:
select distinct date_format(Dat eTimeCreation,' %d %m %Y'), count(distinct
idarticle) from articles group by DateTimeCreatio n order by DateTimeCreatio n
ASC

What I'd like is a table with
date nbre of creation
2006-01-01 3
2006-01-02 2
2006-01-03 5
2006-01-04 7

and so on.....
actually I've:
DateTimeCreatio n
2006-01-01 09:11:03
2006-01-01 12:01:22
2006-01-01 18:42:17
2006-01-02 10:27:55
.....

Thanks for help.

Bob

**Paul Lautman** · Jul 6 '06, 05:35 PM

Re: mysql help needed

Bob Bedford wrote:

Hi all,
>

>SELECT count( DISTINCT a.idperson ) , b.region
>FROM `table1` a
>JOIN `table2` b
>USING ( zip )
>GROUP BY region

>
Now I've an other question, as I can't get it to work, even with your
example.
>
I've a table where there is a datetime field.
>
I'd like to count the number of articles created every day. How to do
so ?
I've tried this:
select distinct date_format(Dat eTimeCreation,' %d %m %Y'),
count(distinct idarticle) from articles group by DateTimeCreatio n
order by DateTimeCreatio n ASC
>
What I'd like is a table with
date nbre of creation
2006-01-01 3
2006-01-02 2
2006-01-03 5
2006-01-04 7
>
and so on.....
actually I've:
DateTimeCreatio n
2006-01-01 09:11:03
2006-01-01 12:01:22
2006-01-01 18:42:17
2006-01-02 10:27:55
....
>
Thanks for help.
>
Bob
select distinct date_format(Dat eTimeCreation,' %d %m %Y'),
count(distinct idarticle) from articles group by DateTimeCreatio n
order by DateTimeCreatio n ASC

I'm a bit confused by a bit of this. You say that you want
date nbre of creation
2006-01-01 3
but the date format in your query is day month year?
Try
SELECT count( idarticle ) , left( DateTimeCreatio n, 10 ) AS created
FROM `articles`
GROUP BY created
or
SELECT count( idarticle ) , date_format(Dat eTimeCreation,' %d %m %Y') AS
created
FROM `articles`
GROUP BY created

Either should work

mysql help needed

mysql help needed

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment