0

I have a table containing weights over time which I want to evaluate as flow:

Scan    TimeStamp       Position  Weight
01      14/11/01 12:00  0         0
01      14/11/01 12:10  10        1.6
02      14/11/01 13:00  0         2.6
02      14/11/01 13:10  10        4.2
...

Now I want to calculate the flow during a scan (begin to end). My query looks like that:

Select MeanTime, TheFlow From
(Select AVG(TheTimeStamp) as MeanTime From flow Where ScanNumber=73),
(Select Weightdiff / TimeSpan as TheFlow From
      (Select (MaxWeight - MinWeight) as WeightDiff From
           (Select Weight as MAXWEIGHT from Flow Where ScanNumber=73 HAVING "POSITION"=MAX("POSITION")),
           (Select Weight as MINWEIGHT from FLOW Where ScanNumber=73 HAVING "POSITION"=MIN("POSITION")),
      (Select (MaxTime - MinTime) * 24 as TimeSpan From
           (Select MAX("THETIMESTAMP") as MaxTime From FLOW Where ScanNumber=73),
           (Select MIN("THETIMESTAMP") as MinTime From Flow Where ScanNumber=73))));

I get an error:

SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

What's wrong?

To clarify my question, I need to extract the following information out of the data:

  1. the mean time between the start (eg. 12:00) and the end eg. 12:10) of a scan (MeanTime) e.g. Scannumber 01), i.e. 12:05
  2. I need the weight difference between end and start
  3. I have to calculate the "Flow" from the weight diff and the time between start and end
  4. All in all I need two data Meantime and flow, which I want to plot (flow over time)
Andriy M
  • 76,112
  • 17
  • 94
  • 154
llnck
  • 1
  • 1
  • what do you mean by "calculate the flow during a scan" ? You mean calculate the weight difference between start and stop? Can you provide desired output? – Eduard Uta Nov 27 '14 at 15:46
  • Desired outut would look like that: 14/11/01 12:05 1.6 and 14/11/01 13:05 1.6 (Mean time of scan, weight at end - weight at start) – llnck Nov 27 '14 at 17:18
  • What is your database system and which version? – Andriy M Nov 29 '14 at 02:47

1 Answers1

0

This should do the job for an individual Scan, which appears to be the requirement.

DECLARE @Scan INT = 1
SELECT 
    MeanTime = DATEADD(SECOND, DATEDIFF(SECOND, FirstScan.TimeStamp, LastScan.TimeStamp), FirstScan.TimeStamp)
    , WeightDifference = LastScan.Weight - FirstScan.Weight
FROM
    (SELECT Position = MIN(Position) FROM Flow WHERE Scan = @Scan) MinScan
    CROSS JOIN (SELECT Position = MAX(Position) FROM Flow WHERE Scan = @Scan) MaxScan
    INNER JOIN Flow FirstScan ON MinScan.Position = FirstScan.Position
        AND FirstScan.Scan = @Scan
    INNER JOIN Flow LastScan ON MaxScan.Position = LastScan.Position
        AND LastScan.Scan = @Scan
Nugsson
  • 196
  • 2
  • 12