| Lists: | pgsql-general |
|---|
| From: | "Castle, Lindsay" <lindsay(dot)castle(at)eds(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | SQL SUM query limited by dates |
| Date: | 2003-07-30 04:01:01 |
| Message-ID: | B09017B65BC1A54BB0B76202F63DDCCA0532490C@auntm201 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Hi All,
A quick select query I'm having some dramas with;
I'm trying to SUM a number of values based on them being the latest entries
based on their date.
Eg I have a table with element (text), date (date) and volume (numeric), it
has 100 entries, I want to grab 30 entries with the most recent dates and
total up the volume information.
I thought something along the lines of:
SELECT SUM(volume) from <tablename>
WHERE element = 'name1'
GROUP BY date
ORDER BY date DESC LIMIT 30
Of course (I believe) this will only sum up anything that has matching
dates.
Can I do this within a SELECT SUM() statement or do I need to look at
aggregate functions?
Or perhaps the HAVING clause could be of use for this one?
My dataset has one row per date and could possibly be out of date order,
hence the order by being a requirement (unless there is another way to
ensure only the most recent dates are accounted for).
Thanks in advance,
Linz
| From: | DeJuan Jackson <djackson(at)speedfc(dot)com> |
|---|---|
| To: | "Castle, Lindsay" <lindsay(dot)castle(at)eds(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: SQL SUM query limited by dates |
| Date: | 2003-07-30 13:20:32 |
| Message-ID: | 3F27C620.4080903@speedfc.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
use a sub-select
SELECT SUM(volume)
FROM (SELECT volumn FROM <tablename>
WHERE element = 'name1' ORDER BY date DESC LIMIT 30) t
Castle, Lindsay wrote:
>Hi All,
>
>A quick select query I'm having some dramas with;
>
>I'm trying to SUM a number of values based on them being the latest entries
>based on their date.
>
>Eg I have a table with element (text), date (date) and volume (numeric), it
>has 100 entries, I want to grab 30 entries with the most recent dates and
>total up the volume information.
>
>I thought something along the lines of:
> SELECT SUM(volume) from <tablename>
> WHERE element = 'name1'
> GROUP BY date
> ORDER BY date DESC LIMIT 30
>
>Of course (I believe) this will only sum up anything that has matching
>dates.
>
>Can I do this within a SELECT SUM() statement or do I need to look at
>aggregate functions?
>Or perhaps the HAVING clause could be of use for this one?
>
>My dataset has one row per date and could possibly be out of date order,
>hence the order by being a requirement (unless there is another way to
>ensure only the most recent dates are accounted for).
>
>Thanks in advance,
>
>Linz
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>