mysql help needed | | |
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,Lausanne
namex,1000,Lausanne1
namex,1000,LausanneN....
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
| | | | re: mysql help needed
Bob Bedford wrote: Quote:
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,Lausanne
namex,1000,Lausanne1
namex,1000,LausanneN....
>
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 | | | | re: mysql help needed
Hi Paul, Quote:
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 | | | | re: mysql help needed
How to do so ? in fact what I need is count the number of peoples in a Quote:
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 | | | | re: mysql help needed
untested...!
select distinct zip,region,count(idperson)
from table1
left join table2 on table2.zip = table 1.zip
group by zip
Bob Bedford wrote: Quote:
Hi Paul,
> Quote:
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
| | | | re: mysql help needed
Bob Bedford wrote: Quote:
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,Lausanne
namex,1000,Lausanne1
namex,1000,LausanneN....
>
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 | | | | re: mysql help needed
strawberry wrote: Quote:
untested...!
>
select distinct zip,region,count(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. | | | | re: mysql help needed
"Paul Lautman" <paul.lautman@btinternet.coma écrit dans le message de
news: 4h425mF1prj87U1@individual.net... Quote:
Bob Bedford wrote: Quote:
>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,Lausanne
>namex,1000,Lausanne1
>namex,1000,LausanneN....
>>
>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. | | | | re: mysql help needed
Bob Bedford wrote: Quote:
Hi Paul,
>
> Quote:
>>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@attglobal.net
================== | | | | re: mysql help needed
Hi all, Quote:
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(DateTimeCreation,'%d %m %Y'), count(distinct
idarticle) from articles group by DateTimeCreation order by DateTimeCreation
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:
DateTimeCreation
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 | | | | re: mysql help needed
Bob Bedford wrote: Quote:
Hi all,
> Quote:
>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(DateTimeCreation,'%d %m %Y'),
count(distinct idarticle) from articles group by DateTimeCreation
order by DateTimeCreation 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:
DateTimeCreation
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(DateTimeCreation,'%d %m %Y'),
count(distinct idarticle) from articles group by DateTimeCreation
order by DateTimeCreation 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( DateTimeCreation, 10 ) AS created
FROM `articles`
GROUP BY created
or
SELECT count( idarticle ) , date_format(DateTimeCreation,'%d %m %Y') AS
created
FROM `articles`
GROUP BY created
Either should work |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
