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! 5 1423
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*******@attgl obal.net
=============== ===
Jerry Stuckle <js*******@attg lobal.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,e lon,0
FROM geo
WHERE id IN
(SELECT MAX(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,0,0,SU M(dist)
FROM geo
GROUP BY name
) AS foo
GROUP BY name;
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Tim Roberts wrote:
Jerry Stuckle <js*******@attg lobal.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,e lon,0
FROM geo
WHERE id IN
(SELECT MAX(id) AS id FROM geo GROUP BY name)
UNION
SELECT name,0,0,0,0,SU M(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*******@attgl obal.net
=============== ===
Jerry Stuckle <js*******@attg lobal.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.
Tim Roberts wrote:
Jerry Stuckle <js*******@attg lobal.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*******@attgl obal.net
=============== === This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dafella |
last post by:
The following is code from Xeoport.
It is suppose to access my pop3 account and load email into Mysql
database.
It's not inserting and there is no log or anything to tell me why.
Here is the page.
http://www.dafella.com/xeoport/
The original coder left no contact info.
The reason I think it's the code is because of another issue.
|
by: pancho |
last post by:
Greetings, I need help configuring/building PHP3 with MySQL as a DSO
on a Solaris 8 box - this module is needed to host some existing
sites I will be migrating
Note. I built PHP4 from source and it loads without an error.
When I try t run "apachectl configtest" I get the following error:
--
Cannot load /usr/local/apache/libexec/libphp3.so...
|
by: Richard Gabriel |
last post by:
Hi everyone,
Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table
corruption often. It happens about twice per week (with about 500
queries per second average). I have even set up a cron to run
mysqlcheck every hour to try to do some damage control. The biggest
problem is that once the table is corrupted, it seems to...
|
by: Mark Adams |
last post by:
I really need some help with this. MySQL will not start on boot despite
everything I've done to make sure that it is set to do so.
When I start it as root from a terminal with "/usr/bin/mysqld_safe
--skip-grant-tables &" I show several mysqld procs. and one mysqld_safe,
but I can't connect:
# ps -aux| grep mysql
Warning: bad syntax,...
|
by: Mike Chirico |
last post by:
Hopefully this will help someone...
Helpful Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Last Updated: Fri Apr 16 11:47:34 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
Format is better on the above link. I'm looking for...
| |
by: pratchaya |
last post by:
This is my sample error in my MySQL Log
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please
do
resolve it
Trying to get...
|
by: baustin75 |
last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie
only when debugging in php designer 2005
--------------------------------------------------------------------------------
Hello,
I have a very simple problem but cannot seem to figure it out. I have a
very simple php script that sends a test email to myself. When I...
|
by: Mark |
last post by:
the Following bit of code doesn't work.
It seems to respond to the second, starting with 'add iif statement
for Good Practice', but not to the first, starting 'add iif statement
for archived'
Help me to sort this out, it has taken me almost a week to wade
through and it still won't work.
Select Case Forms("frmForce").OpenArgs
|
by: sheree |
last post by:
I would like to create a query where one of the columns of the queries
comes from a combo list box on a form.
For example, if my table has the following fields:
id
name
interest1
interest2
interest3
my combo box would be a dropdown containing these choices:
|
by: DarkGiank |
last post by:
Hi, im new to csharp and im trying to create a class that can change the application database without no rewriting all connection code... but cause some reason it is not working... it tells me that im not creating the object but im doing it,,, please help im a newbie to c#
using System;
using System.Collections.Generic;
using System.Text;...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |