UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Return Min, Avg, Max And Last Value Of A Column, Any Versions    
 
   
ordnance1
post Jul 14 2019, 10:00 AM
Post#1



Posts: 692
Joined: 7-May 11



I have a table with to columns RecordStamp and TempOut. What I would like is a query that gives me the min, avg and max TempOut in addition to the last TempOut
Go to the top of the page
 
theDBguy
post Jul 14 2019, 10:30 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,566
Joined: 19-June 07
From: SunnySandyEggo


Hi. How do you know which one is the "last" TempOut? Do you have a Date/Time field?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Jul 14 2019, 02:18 PM
Post#3



Posts: 692
Joined: 7-May 11



Yes the RecordStamp field is a date/time field
Go to the top of the page
 
MadPiet
post Jul 14 2019, 04:47 PM
Post#4



Posts: 3,361
Joined: 27-February 09



Can you post the CREATE TABLE script for that you're getting the data from?

If the date values are always increasing (for example if you're taking measurements over time), the "last value" is also the highest/max date value. So you could use MAX([Datefield]).
Go to the top of the page
 
MadPiet
post Jul 15 2019, 09:35 AM
Post#5



Posts: 3,361
Joined: 27-February 09



Here's some fake data... yeah, not much of it, but it should give you the idea:
use tempdb;
go

CODE
CREATE TABLE Readings (
    ReadingTimeStamp    DATETIME,
    TempOut        TINYINT
);
GO

INSERT INTO Readings (ReadingTimeStamp, TempOut)
VALUES ('7/15/2019 8:00', 41),('7/15/2019 8:15',43),('7/15/2019 8:30',45)
,('7/15/2019 8:45',42);

SELECT MIN(TempOut) As MinTemp,
    MAX(TempOut) AS MaxTemp,
    AVG(TempOut) As AvgTemp,
    MAX(ReadingTimeStamp) AS LastReadingTime
FROM Readings;


?
This post has been edited by MadPiet: Jul 15 2019, 09:39 AM
Go to the top of the page
 
ordnance1
post Jul 15 2019, 12:09 PM
Post#6



Posts: 692
Joined: 7-May 11



Thank you for that but what I need in addition to the min, avg and max, is the last temperature added to the table.
Go to the top of the page
 
cheekybuddha
post Jul 15 2019, 01:00 PM
Post#7


UtterAccess VIP
Posts: 11,684
Joined: 6-December 03
From: Telegraph Hill


Hi,

Try:
CODE
SELECT
  MIN(r1.TempOut) As MinTemp,
  MAX(r1.TempOut) AS MaxTemp,
  AVG(r1.TempOut) As AvgTemp,
  (
    SELECT TOP 1
      r2.TempOut
    FROM Readings r2
    WHERE r2.ReadingTimeStamp = MAX(r1.ReadingTimeStamp)
  ) AS LastReading
FROM Readings r1;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th November 2019 - 07:35 PM