|
Posted by Jerry Stuckle on July 27, 2008, 10:16 am
Please log in for more thread options Tim Roberts wrote:
>> 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.
jstucklex@attglobal.net
==================
| 
XML Sitemap