Home » SQL & PL/SQL » SQL & PL/SQL » Summing everything in the table on each row.
Summing everything in the table on each row. [message #203855] Thu, 16 November 2006 10:59 Go to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
I want to write a code to 1st group by airplane and then sum up all the flight hours in the table for that airplane each time it hits a new date.

For example: A plane has 150 flight records. Each record is a new flight that has flight time. I want to sum up all the flights for this airplane from the time in this row and return a total aircraft time on the same row.

Repeat the same process on the next row. I need the totals to start over when it hits a new plane.

Any ideas.

I know very little about SQL so I need good examples.

Thanks
Sandy
Re: Summing everything in the table on each row. [message #203857 is a reply to message #203855] Thu, 16 November 2006 11:35 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
crackerbox wrote on Thu, 16 November 2006 11:59

I know very little about SQL so I need good examples.



Then that question should be asked in the newbie forum, don't you think?
Search for analytical functions and running sums. You have to show some effort.
Re: Summing everything in the table on each row. [message #203859 is a reply to message #203857] Thu, 16 November 2006 11:41 Go to previous messageGo to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
Maybe so but I figured that more experienced users visited this section.
Re: Summing everything in the table on each row. [message #203864 is a reply to message #203859] Thu, 16 November 2006 12:51 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
At least give us some sample data (with create table and insert statements) and the expected result set.
Re: Summing everything in the table on each row. [message #203869 is a reply to message #203864] Thu, 16 November 2006 13:53 Go to previous messageGo to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
Here is a sample data set. The first three columns are in a table. The last column is the column I'm trying to get and I need the number to reset on each group (AirNo). I am trying to get the total flights hours from each date until now. For example: Aircraft No 38 has flown a total of 8 hours from 7/2/06 until now (or the lastest flight in the table for that plane).

AirNo DEPARTING_DATE Hrs Total Needed
38 7/2/2006 0:30 3 8
38 7/10/2006 23:43 1 5
38 9/11/2006 21:35 2 4
38 9/12/2006 14:40 1 2
38 9/14/2006 0:50 1 1
39 7/2/2006 16:26 4 7
39 9/1/2006 11:54 2 3
39 9/2/2006 14:58 1 1
40 7/5/2006 17:17 3 8
40 7/7/2006 15:56 2 5
40 7/21/2006 11:06 2 3
40 7/24/2006 14:30 1 1



Will this be enough code to help me out? Performance issues are a problem because we have a very large database. Is a stored procedure the best way to go or would writing the code in a view table be better?

CREATE PROCEDURE [dbo].[sp_Aircraft_Flight_Hrs] AS



Select Distinct A.AirNo, C.Departing_Date,C.Hours

From Aircraft_Hdr A, Flight_Cycle_Count C

Where A.Doc_No C.Flight_Log_Doc_No
GO

Thank you so much.
Sandy
Re: Summing everything in the table on each row. [message #203965 is a reply to message #203859] Fri, 17 November 2006 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The sections are named for the complexity of the question, not the competence of the answerer.
Re: Summing everything in the table on each row. [message #203968 is a reply to message #203965] Fri, 17 November 2006 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to read up about the joys of Analytic Functions

create table test_air (AirNo number, DEPARTING_DATE date, Hrs number);

insert into test_air values(38, to_date('7/2/2006 0:30','mm/dd/yyyy hh24:mi')   ,3 );
insert into test_air values(38, to_date('7/10/2006 23:43','mm/dd/yyyy hh24:mi') ,1 );
insert into test_air values(38, to_date('9/11/2006 21:35','mm/dd/yyyy hh24:mi') ,2 );
insert into test_air values(38, to_date('9/12/2006 14:40','mm/dd/yyyy hh24:mi') ,1 );
insert into test_air values(38, to_date('9/14/2006 0:50','mm/dd/yyyy hh24:mi')  ,1 );
insert into test_air values(39, to_date('7/2/2006 16:26','mm/dd/yyyy hh24:mi')  ,4 );
insert into test_air values(39, to_date('9/1/2006 11:54','mm/dd/yyyy hh24:mi')  ,2 );
insert into test_air values(39, to_date('9/2/2006 14:58','mm/dd/yyyy hh24:mi')  ,1 );
insert into test_air values(40, to_date('7/5/2006 17:17','mm/dd/yyyy hh24:mi')  ,3 );
insert into test_air values(40, to_date('7/7/2006 15:56','mm/dd/yyyy hh24:mi')  ,2 );
insert into test_air values(40, to_date('7/21/2006 11:06','mm/dd/yyyy hh24:mi') ,2 );
insert into test_air values(40, to_date('7/24/2006 14:30','mm/dd/yyyy hh24:mi') ,1 );

select airno
      ,departing_date
      ,sum(hrs) over (partition by airno 
                      order     by departing_Date 
                      rows between current row and unbounded following) total
from   test_air;

     AIRNO DEPARTING      TOTAL
---------- --------- ----------
        38 02-JUL-06          8
        38 10-JUL-06          5
        38 11-SEP-06          4
        38 12-SEP-06          2
        38 14-SEP-06          1
        39 02-JUL-06          7
        39 01-SEP-06          3
        39 02-SEP-06          1
        40 05-JUL-06          8
        40 07-JUL-06          5
        40 21-JUL-06          3
        40 24-JUL-06          1
Re: Summing everything in the table on each row. [message #204052 is a reply to message #203968] Fri, 17 November 2006 08:41 Go to previous messageGo to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
How can I do this and reference the insert into test_air values
fields from another table. There are over a million flight records in our database.

Re: Summing everything in the table on each row. [message #204064 is a reply to message #204052] Fri, 17 November 2006 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Umm, the create table and insert statements are there to provide you with a runnable example.
You just need to replace "TEST_AIR" in the Select statement with the name of the table in your schema - probably "Aircraft_Hdr" looking at your examples.

If you'd provided us with a CREATE TABLE statement (like you were asked to) then I'd have used your table name in my Sql.
Re: Summing everything in the table on each row. [message #204067 is a reply to message #204064] Fri, 17 November 2006 10:51 Go to previous messageGo to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
I think you misunderstood my question. I was referring to the

insert into test_air values(38, to_date('7/2/2006 0:30','mm/dd/yyyy hh24:mi') ,3 );
statement.

How can I get this work to use Airno (the field) in place of 38, and Departing_Date (the field) instead of to_date('7/2/2006 0:30','mm/dd/yyyy hh24:mi') and the Hours instead of 3.



Re: Summing everything in the table on each row. [message #204086 is a reply to message #204067] Fri, 17 November 2006 13:18 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Shocked Shocked Dead
Re: Summing everything in the table on each row. [message #204095 is a reply to message #204086] Fri, 17 November 2006 14:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
plain silence....
Re: Summing everything in the table on each row. [message #204305 is a reply to message #204067] Mon, 20 November 2006 01:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid you've lost me - I don't understand what you're asking at all.
Actually, I've got a nasty suspicion that you think that you need to extract the data from your tables, put it into a single table, and then run my query against that, in which case you are so very very wrong.

The table TEST_AIR is not an integral part of the solution to your posted problem, it is just a temporary table that I created to demonstrate a solution. Based on the fragments of table definitions that you have provided us with so far, you need to do something like this
select airno
      ,departing_date
      ,sum(hours) over (partition by airno 
                        order     by departing_Date 
                        rows between current row and unbounded following) total
from   (Select Distinct 
               A.AirNo
              ,C.Departing_Date
              ,C.Hours
        From   Aircraft_Hdr A
              ,Flight_Cycle_Count C
        Where  A.Doc_No = C.Flight_Log_Doc_No 
        And    <any other conditions you may need>)
Re: Summing everything in the table on each row. [message #207431 is a reply to message #204305] Tue, 05 December 2006 09:27 Go to previous messageGo to next message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
I'v got the following code using your example:
select Aircraft_Doc_No
,Actual_departing_date,Flight_Hours
,sum(Flight_hours) over partition by airno
order by Actual_departing_Date
rows between current row and unbounded following) total




from (Select Distinct
F.Aircraft_Doc_No
,F.Actual_Departing_Date
,C.Flight_Hours
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

Re: Summing everything in the table on each row. [message #207434 is a reply to message #204095] Tue, 05 December 2006 09:42 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Frank wrote on Fri, 17 November 2006 14:35
plain silence....


Or did you mean Plane silence Laughing
Re: Summing everything in the table on each row. [message #207498 is a reply to message #207431] Tue, 05 December 2006 16:01 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
crackerbox wrote on Tue, 05 December 2006 10:27
I'v got the following code using your example:

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.



That's because this is an Oracle forum and hence and Oracle analytic function. What is SQL 2000?
Re: Summing everything in the table on each row. [message #207586 is a reply to message #207498] Wed, 06 December 2006 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's another name for MS SQL Server 2000.
I'm afraid you're out of luck - SQl Server 2005 includes support for the ANSI standard Analytic functions that we're using here, but 2000 doesn't.
Re: Summing everything in the table on each row. [message #207652 is a reply to message #207498] Wed, 06 December 2006 07:43 Go to previous message
crackerbox
Messages: 7
Registered: November 2006
Junior Member
We're using Microsoft SQL Server 2000 and I'm using Enterprise Manager to write the code.
Previous Topic: creating a dsn problem - microsoft odbc for oracle
Next Topic: Find & Replace
Goto Forum:
  


Current Time: Wed Dec 07 06:58:09 CST 2016

Total time taken to generate the page: 0.27614 seconds