Обсуждение: SQL SUM query limited by dates

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

If all you want is the sum of the last 30 then just don't use the group by.
hth,
- Stuart
P.S. Sorry about format change caused by disclaimer adder

> -----Original Message-----
> From: Castle, Lindsay [mailto:lindsay.castle@eds.com]
> Sent: 30 July 2003 05:01
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] SQL SUM query limited by dates
>
> 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
>

DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.  Access to this message by anyone else is unauthorised.  If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.  Please immediately contact the sender if you have received this message in error. Thank you.

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