From: Matthew Smith <mps@utas.edu.au>
To: pgsql-sql@postgresql.org
Subject: Help on a complex query (avg data for day of the week)
Date: Mon, 19 Dec 2005 16:57:48 +1100
Message-ID: <200512191657.48139.mps@utas.edu.au> (raw)
Hello,
I have a table containing a timestamp and data usage fields (among others).
This table stores amounts of data usage and the times then the data was used,
eg:
time | data
------------------------+----------
2005-03-26 09:32:43+11 | 162
I want to form a query that returns the average total usage for each day of
the week, eg:
day | avg_usage
--------+---------------------
0 | 35684624.000000000
1 | 103344529.000000000
2 | 105899406.000000000
3 | 21994539.000000000
4 | 113045173.000000000
5 | 110675115.000000000
6 | 8791397.0000000000
(7 rows)
To get this info, I am using the following query:
select dow as day, sum(sum_data)/count(dow) as avg_usage from
(select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
sum_data
from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by dow)
as avg_data_per_day group by day;
This works well, assuming that there is at least one entry in the table for
each day in the time period.
The problem comes when there are days where no data is logged. In my example,
the total data for each day of the week is divided by the number of the days
found. As there are exactly 2 of each day of the week between 2005-09-11 and
2005-09-25, we should hope to divide each total by 2. but if there is no data
logged for the 14th, then the total for wednesdays would be divided by 1. I
want it to be the sum of the 2 days divided by 2: (101994539 + 0)/2.
Is there a better way to do this? Or does anyone have any suggestions on the
best way to insert the missing dates into my query?
Any help would be great!
Thanks,
Matthew Smith
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mps@utas.edu.au
Subject: Re: Help on a complex query (avg data for day of the week)
In-Reply-To: <200512191657.48139.mps@utas.edu.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox