Re: SQL SUM query limited by dates

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
>
>