SQL SUM query limited by dates

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2003-07-30 04:46:56 Re: substring implementation (long string)
Previous Message btober 2003-07-30 03:32:12 Re: Does the block of code in a stored procedure execute