Home » SQL & PL/SQL » SQL & PL/SQL » AVG per hour
AVG per hour [message #309694] Fri, 28 March 2008 08:27 Go to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Hi there,
I would like know how to solve the AVG value from a table.

Say I have a table with two columns HOUR & VOLUME containing 7 days of data.

* [Hour] range from 1-48 for 7 days
* [Volume] of water pumped out per half hour.
-----------------
HOUR | VOLUME
1 | 0.03451
2 | 0.01342
3 | 0.452345
. |
. |
48 | 0.452345
1 | 0.562
2 | 0.513
3 | 0.131
. |
. |
48 | 0.1341

How do you work out the average volume of water pumped out for each half hour if you have a table like that?

Regards,

Giorgio

Re: AVG() on a nested table [message #309696 is a reply to message #309694] Fri, 28 March 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't hijack a topic create your own
2/ read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
3/ Always post your Oracle version (4 decimals).
4/ Post what you already tried
5/ Post a test case: create table and insert statements.

Regards
Michel
Re: AVG per hour [message #309699 is a reply to message #309694] Fri, 28 March 2008 08:53 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
[Hour] range from 1-48 for 7 days

Does it mean that "hour" column, actually, contains 30-minutes data (and not hourly volumes)? Because, which day has 48 hours? If that's so, it looks quite simple: select hour and average volume grouped by hour.
Re: AVG per hour [message #314548 is a reply to message #309694] Thu, 17 April 2008 02:26 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Sorry guys, I forgot to include more information

The table that I have consist of data from the past 2wks.
I intend to calculate the average of volume for each half hour value from 1..48 from the past two weeks based on day type
e.g. IF tomorrow is Friday then get the average of half-hour volume from Friday-1 and Friday-2 of last 2wks for each TMNL,PUMP and RETAIL..there are hundreds of TMNL, PUMP and RETAIL in the database, so important to include in the group by rather than just using statement:
SELECT HOUR, AVG(VOLUME) from my_btl 
group by HOUR


TMNL	PUMP	RETAIL	LSTDATE	HOUR	VOLUME	LSTDATE	HOUR	VOLUME		AVERAGE
SYDWTR	BZCGYTG	BZKR	4-Apr	1	0.013953	11-Apr	1	0.013953		0.027906
SYDWTR	BZCGYTG	BZKR	4-Apr	2	0.009477	11-Apr	2	0.009477		0.018954
SYDWTR	BZCGYTG	BZKR	4-Apr	3	0.008933	11-Apr	3	0.008933		0.017866


Sample data
TMNL	PUMP	RETAIL	LSTDATE	HOUR	VOLUME	LSTDATE	HOUR	VOLUME
SYDWTR	BZCGYTG	BZKR	4-Apr	1	0.013953	11-Apr	1	0.013953
SYDWTR	BZCGYTG	BZKR	4-Apr	2	0.009477	11-Apr	2	0.009477
SYDWTR	BZCGYTG	BZKR	4-Apr	3	0.008933	11-Apr	3	0.008933
SYDWTR	BZCGYTG	BZKR	4-Apr	4	0.008648	11-Apr	4	0.008648
SYDWTR	BZCGYTG	BZKR	4-Apr	5	0.006864	11-Apr	5	0.006864
SYDWTR	BZCGYTG	BZKR	4-Apr	6	0.009808	11-Apr	6	0.009808
SYDWTR	BZCGYTG	BZKR	4-Apr	7	0.005882	11-Apr	7	0.005882
SYDWTR	BZCGYTG	BZKR	4-Apr	8	0.005518	11-Apr	8	0.005518
SYDWTR	BZCGYTG	BZKR	4-Apr	9	0.011181	11-Apr	9	0.011181
SYDWTR	BZCGYTG	BZKR	4-Apr	10	0.009231	11-Apr	10	0.009231
SYDWTR	BZCGYTG	BZKR	4-Apr	11	0.012839	11-Apr	11	0.012839
SYDWTR	BZCGYTG	BZKR	4-Apr	12	0.015976	11-Apr	12	0.015976
SYDWTR	BZCGYTG	BZKR	4-Apr	13	0.007633	11-Apr	13	0.007633
SYDWTR	BZCGYTG	BZKR	4-Apr	14	0.004483	11-Apr	14	0.004483
SYDWTR	BZCGYTG	BZKR	4-Apr	15	0.098608	11-Apr	15	0.098608
SYDWTR	BZCGYTG	BZKR	4-Apr	16	0.004801	11-Apr	16	0.004801
SYDWTR	BZCGYTG	BZKR	4-Apr	17	0.005816	11-Apr	17	0.005816
SYDWTR	BZCGYTG	BZKR	4-Apr	18	0.006678	11-Apr	18	0.016695
SYDWTR	BZCGYTG	BZKR	4-Apr	19	0.006234	11-Apr	19	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	20	0.006234	11-Apr	20	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	21	0.006234	11-Apr	21	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	22	0.006234	11-Apr	22	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	23	0.006234	11-Apr	23	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	24	0.050334	11-Apr	24	0.125835
SYDWTR	BZCGYTG	BZKR	4-Apr	25	0.005534	11-Apr	25	0.013835
SYDWTR	BZCGYTG	BZKR	4-Apr	26	0.006234	11-Apr	26	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	27	0.006234	11-Apr	27	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	28	0.006234	11-Apr	28	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	29	0.006234	11-Apr	29	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	30	0.006234	11-Apr	30	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	31	0.006234	11-Apr	31	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	32	0.006234	11-Apr	32	0.015585
SYDWTR	BZCGYTG	BZKR	4-Apr	33	0.000477	11-Apr	33	0.000477
SYDWTR	BZCGYTG	BZKR	4-Apr	34	0.008933	11-Apr	34	0.008933
SYDWTR	BZCGYTG	BZKR	4-Apr	35	0.008648	11-Apr	35	0.008648
SYDWTR	BZCGYTG	BZKR	4-Apr	36	0.006864	11-Apr	36	0.006864
SYDWTR	BZCGYTG	BZKR	4-Apr	37	0.009808	11-Apr	37	0.009808
SYDWTR	BZCGYTG	BZKR	4-Apr	38	0.005882	11-Apr	38	0.005882
SYDWTR	BZCGYTG	BZKR	4-Apr	39	0.005518	11-Apr	39	0.005518
SYDWTR	BZCGYTG	BZKR	4-Apr	40	0.011181	11-Apr	40	0.011181
SYDWTR	BZCGYTG	BZKR	4-Apr	41	0.009231	11-Apr	41	0.009231
SYDWTR	BZCGYTG	BZKR	4-Apr	42	0.012839	11-Apr	42	0.012839
SYDWTR	BZCGYTG	BZKR	4-Apr	43	0.015976	11-Apr	43	0.015976
SYDWTR	BZCGYTG	BZKR	4-Apr	44	0.007633	11-Apr	44	0.007633
SYDWTR	BZCGYTG	BZKR	4-Apr	45	0.004483	11-Apr	45	0.004483
SYDWTR	BZCGYTG	BZKR	4-Apr	46	0.008608	11-Apr	46	0.008608
SYDWTR	BZCGYTG	BZKR	4-Apr	47	0.004801	11-Apr	47	0.004801
SYDWTR	BZCGYTG	BZKR	4-Apr	48	0.005816	11-Apr	48	0.005816


I used the following to try to work out the Average but it turned out to be incorrect aggregate volume when compared to manually calculating it via excel i.e. volume1 + volume2 /2

select TMNL, Pump, Retailer, HH, AVG(VOLUME) from my_btl
where day = day >= sysdate-14;
group by TMNL, Pump, Retailer, HH
order by TMNL;

[Updated on: Thu, 17 April 2008 02:37]

Report message to a moderator

Re: AVG per hour [message #314552 is a reply to message #314548] Thu, 17 April 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can't have several times the same column in a table
2/ Your query is syntaxically wrong
3/ I don't see Oracle version
4/ I don't see create table and insert statements.

3 weeks to NOT answer the questions we asked, you are not very efficient.

Regards
Michel
Re: AVG per hour [message #314558 is a reply to message #314552] Thu, 17 April 2008 02:54 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Quote:

1/ You can't have several times the same column in a table
2/ Your query is syntaxically wrong
3/ I don't see Oracle version
4/ I don't see create table and insert statements.


1/ The colums in my table is as follow

TMNL PUMP RETAIL LSTDATE HOUR VOLUME

2/ where day >= sysdate-14;

3/ Oracle Version 10
4/ I deem it as unnecessary to include in this query

Quote:
3 weeks to NOT answer the questions we asked, you are not very efficient.

Sorry, it's due relocation of my broadband, ISP took over 21 days to finally get my broadband relocated to new address.
Re: AVG per hour [message #314580 is a reply to message #314558] Thu, 17 April 2008 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2/ where day >= sysdate-14;

Still syntaxically wrong

Regards
Michel
Re: AVG per hour [message #314590 is a reply to message #314580] Thu, 17 April 2008 04:06 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Quote:
Still syntaxically wrong

So how do I rectify it?

CREATE TABLE my_tbl 
(
TMNL    VARCHAR2(25 BYTE),
PUMP    VARCHAR2(25 BYTE),
RETAIL  VARCHAR2(25 BYTE),
LSTDATE DATE,
HOUR    NUMBER,
VOLUME  NUMBER
);
                     

insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',1,0.013953);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',2,0.009477);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',3,0.008933);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',4,0.008648);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',5,0.006864);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',6,0.009808);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',7,0.005882);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',8,0.005518);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',9,0.011181);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',10,0.009231);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',11,0.012839);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',12,0.015976);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',13,0.007633);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',14,0.004483);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',15,0.098608);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',16,0.004801);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',17,0.005816);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',18,0.006678);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',19,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',20,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',21,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',22,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',23,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',24,0.050334);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',25,0.005534);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',26,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',27,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',28,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',29,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',30,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',31,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',32,0.006234);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',33,0.000477);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',34,0.008933);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',35,0.008648);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',36,0.006864);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',37,0.009808);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',38,0.005882);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',39,0.005518);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',40,0.011181);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',41,0.009231);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',42,0.012839);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',43,0.015976);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',44,0.007633);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',45,0.004483);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',46,0.008608);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',47,0.004801);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','4-Apr-2008',48,0.005816);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',1,0.013953);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',2,0.009477);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',3,0.008933);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',4,0.008648);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',5,0.006864);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',6,0.009808);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',7,0.005882);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',8,0.005518);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',9,0.011181);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',10,0.009231);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',11,0.012839);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',12,0.015976);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',13,0.007633);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',14,0.004483);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',15,0.098608);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',16,0.004801);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',17,0.005816);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',18,0.016695);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',19,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',20,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',21,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',22,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',23,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',24,0.125835);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',25,0.013835);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',26,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',27,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',28,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',29,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',30,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',31,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',32,0.015585);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',33,0.000477);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',34,0.008933);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',35,0.008648);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',36,0.006864);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',37,0.009808);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',38,0.005882);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',39,0.005518);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',40,0.011181);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',41,0.009231);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',42,0.012839);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',43,0.015976);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',44,0.007633);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',45,0.004483);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',46,0.008608);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',47,0.004801);
insert into my_tbl values('SYDWTR','BZCGYTG','BZKR','11-Apr-2008',48,0.005816);
Re: AVG per hour [message #314595 is a reply to message #314590] Thu, 17 April 2008 04:17 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
it works by having a simple SQL statement
select HOUR, AVG(VOLUME)
from my_tbl
group by        HOUR
order by hour;


But if I populate the database with data from diff. dates then it won't give the corect aggregate value by day e.g. Friday.

[Updated on: Thu, 17 April 2008 04:36]

Report message to a moderator

Re: AVG per hour [message #314752 is a reply to message #314595] Thu, 17 April 2008 12:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you mean that you want separate averages for the Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, and Sundays, so for a 2-week period, you would average two of each, then you can use to_char to extract the day as MON, TUE, WED, THU, FRI, SAT, SUN, then you can include that column in your select and group by, and order by clauses, just as you did the hour column:


SELECT TO_CHAR (lstdate, 'DY') AS day_of_week, hour, AVG (volume)
FROM   my_tbl
WHERE  lstdate >= SYSDATE - 14
GROUP  BY TO_CHAR (lstdate, 'DY'), hour
ORDER  BY TO_CHAR (lstdate, 'DY'), hour;
Re: AVG per hour [message #314831 is a reply to message #314752] Thu, 17 April 2008 23:55 Go to previous message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Thank you Barbara, that answered my question.
Previous Topic: STRING TO RAW
Next Topic: Inserting script name in table?
Goto Forum:
  


Current Time: Wed Dec 07 18:15:38 CST 2016

Total time taken to generate the page: 0.07077 seconds