Help with some PHP/mySQL

[inexion]

Hello,

I'll try to explain what I'm trying to accomplish here, hopefully not
too bad a task. Basically I have an existing table with data that will
be operated on to form new records etc...

for example:
id name slat slon elat elon dist
1 65 -10.32 136.64 -10.18 136.69 6556.52
2 65 -10.18 136.69 -10.16 136.72 1701.68
3 65 -10.16 136.72 -10.11 136.72 2106.7
4 66 -10.13 136.73 -10.05 136.79 4536.58
5 66 -10.05 136.79 -10.06 136.82 1082.5
6 66 -10.06 136.82 -10.01 136.84 2238.2

So what I need to do here is basically sum all of the values in the
dist column that correspond to any value with the same name value (eg,
only 65's), plus take both the slat & slon values from the first
distinct record (eg, the 1st 65 slat & slon combined with the last 65
elat & elon values....where 1st would be id#1, last would be id#3
etc.) and combine them with the last elat & elon values...

So from above, my resulting query would give me 2 results looking like
so...
id name slat slon elat elon totaldist
1 65 -10.32 136.64 -10.11 136.72 10364.9
2 66 -10.13 136.73 -10.01 136.84 7857.28
any help would be greatly appreciated, thanks!

[Jerry Stuckle]

inexion wrote:

Hello,

I'll try to explain what I'm trying to accomplish here, hopefully not
too bad a task. Basically I have an existing table with data that will
be operated on to form new records etc...

for example:
id name slat slon elat elon dist
1 65 -10.32 136.64 -10.18 136.69 6556.52
2 65 -10.18 136.69 -10.16 136.72 1701.68
3 65 -10.16 136.72 -10.11 136.72 2106.7
4 66 -10.13 136.73 -10.05 136.79 4536.58
5 66 -10.05 136.79 -10.06 136.82 1082.5
6 66 -10.06 136.82 -10.01 136.84 2238.2

So what I need to do here is basically sum all of the values in the
dist column that correspond to any value with the same name value (eg,
only 65's), plus take both the slat & slon values from the first
distinct record (eg, the 1st 65 slat & slon combined with the last 65
elat & elon values....where 1st would be id#1, last would be id#3
etc.) and combine them with the last elat & elon values...

So from above, my resulting query would give me 2 results looking like
so...
id name slat slon elat elon totaldist
1 65 -10.32 136.64 -10.11 136.72 10364.9
2 66 -10.13 136.73 -10.01 136.84 7857.28
any help would be greatly appreciated, thanks!

Much easier to do it in SQL. Try comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Tim Roberts]

Jerry Stuckle <js*******@attglobal.netwrote:

>inexion wrote:

>Hello,

I'll try to explain what I'm trying to accomplish here, hopefully not
too bad a task. Basically I have an existing table with data that will
be operated on to form new records etc...

for example:
id name slat slon elat elon dist
1 65 -10.32 136.64 -10.18 136.69 6556.52
2 65 -10.18 136.69 -10.16 136.72 1701.68
3 65 -10.16 136.72 -10.11 136.72 2106.7
4 66 -10.13 136.73 -10.05 136.79 4536.58
5 66 -10.05 136.79 -10.06 136.82 1082.5
6 66 -10.06 136.82 -10.01 136.84 2238.2

So what I need to do here is basically sum all of the values in the
dist column that correspond to any value with the same name value (eg,
only 65's), plus take both the slat & slon values from the first
distinct record (eg, the 1st 65 slat & slon combined with the last 65
elat & elon values....where 1st would be id#1, last would be id#3
etc.) and combine them with the last elat & elon values...

So from above, my resulting query would give me 2 results looking like
so...
id name slat slon elat elon totaldist
1 65 -10.32 136.64 -10.11 136.72 10364.9
2 66 -10.13 136.73 -10.01 136.84 7857.28
any help would be greatly appreciated, thanks!

Much easier to do it in SQL. Try comp.databases.mysql.

Hmm, I'm not sure I would say it is "much easier". It's certainly possible
to write a union query for this, but it's probably easier to maintain by
doing a simple query in SQL and the math in PHP.

This works in Postgres.

SELECT name,SUM(slat) AS slat,SUM(slon) AS slon,
SUM(elat) AS elat,SUM(elon) AS elon,SUM(dist) AS dist FROM
(
SELECT name,slat,slon,0 AS elat,0 AS elon,0 AS dist
FROM geo
WHERE id IN
(SELECT MIN(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,elat,elon,0
FROM geo
WHERE id IN
(SELECT MAX(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,0,0,SUM(dist)
FROM geo
GROUP BY name
) AS foo
GROUP BY name;
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.

[Jerry Stuckle]

Tim Roberts wrote:

Jerry Stuckle <js*******@attglobal.netwrote:

>inexion wrote:

>>Hello,

I'll try to explain what I'm trying to accomplish here, hopefully not
too bad a task. Basically I have an existing table with data that will
be operated on to form new records etc...

for example:
id name slat slon elat elon dist
1 65 -10.32 136.64 -10.18 136.69 6556.52
2 65 -10.18 136.69 -10.16 136.72 1701.68
3 65 -10.16 136.72 -10.11 136.72 2106.7
4 66 -10.13 136.73 -10.05 136.79 4536.58
5 66 -10.05 136.79 -10.06 136.82 1082.5
6 66 -10.06 136.82 -10.01 136.84 2238.2

So what I need to do here is basically sum all of the values in the
dist column that correspond to any value with the same name value (eg,
only 65's), plus take both the slat & slon values from the first
distinct record (eg, the 1st 65 slat & slon combined with the last 65
elat & elon values....where 1st would be id#1, last would be id#3
etc.) and combine them with the last elat & elon values...

So from above, my resulting query would give me 2 results looking like
so...
id name slat slon elat elon totaldist
1 65 -10.32 136.64 -10.11 136.72 10364.9
2 66 -10.13 136.73 -10.01 136.84 7857.28
any help would be greatly appreciated, thanks!

Much easier to do it in SQL. Try comp.databases.mysql.

Hmm, I'm not sure I would say it is "much easier". It's certainly possible
to write a union query for this, but it's probably easier to maintain by
doing a simple query in SQL and the math in PHP.

This works in Postgres.

SELECT name,SUM(slat) AS slat,SUM(slon) AS slon,
SUM(elat) AS elat,SUM(elon) AS elon,SUM(dist) AS dist FROM
(
SELECT name,slat,slon,0 AS elat,0 AS elon,0 AS dist
FROM geo
WHERE id IN
(SELECT MIN(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,elat,elon,0
FROM geo
WHERE id IN
(SELECT MAX(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,0,0,SUM(dist)
FROM geo
GROUP BY name
) AS foo
GROUP BY name;

Sorry to hear you need a UNION to do it in PostGres. I thought PostGres
was better than that. MySQL can do it without such nonsense.

But this is a PHP newsgroup, not a SQL one. If he's using MySQL, others
(and I) will be glad to help him on comp.databases.mysq.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Tim Roberts]

Jerry Stuckle <js*******@attglobal.netwrote:

>
Sorry to hear you need a UNION to do it in PostGres. I thought PostGres
was better than that. MySQL can do it without such nonsense.

I'm not convinced that it can be done in standard SQL without a UNION, or
an even worse set of nested SEELCTs.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.

[Jerry Stuckle]

Tim Roberts wrote:

Jerry Stuckle <js*******@attglobal.netwrote:

>Sorry to hear you need a UNION to do it in PostGres. I thought PostGres
was better than that. MySQL can do it without such nonsense.

I'm not convinced that it can be done in standard SQL without a UNION, or
an even worse set of nested SEELCTs.

Which is still off topic in this newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================