CODEWITHSUNDEEP
postgresqlsum
Elena_K
Elena_K

Reputation: 73

Calculate daily sums in PostgreSQL

I am fairly new in postgres and what am trying to do is calculate sum values for each day for every month (i.e daily sum values). Based on scattering information I came up with something like this: 

CREATE OR REPLACE FUNCTION sumvalues() RETURNS double precision AS
$BODY$
BEGIN
   FOR i IN 0..31 LOOP
   SELECT SUM("Energy") 
   FROM "public"."EnergyWh" e
   WHERE  e."DateTime" = day('01-01-2005 00:00:00'+ INTERVAL 'i' DAY);
   END LOOP;

END
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.sumvalues()
  OWNER TO postgres;

The query returned successfully, so I thought I had made it. However when am trying to insert the values of the function to a table (which maybe wrong):

INSERT INTO "SumValues"
("EnergyDC") 

    (
     SELECT sumvalues()
     );

I get this:

ERROR: invalid input syntax for type interval: "01-01-2005 00:00:00" LINE 3: WHERE e."DateTime" = day('01-01-2005 00:00:00'+ INTERVAL...

I tried to debug it myself but yet am not sure, which of the two I am doing wrong (or both) and why.

Here is an example of EnergyWh

(am using systemid and datetime as composite PK, but that should not matter)

Upvotes: 3

Views: 4791

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45795

see GROUP BY clause http://www.postgresql.org/docs/9.2/static/tutorial-agg.html

SELECT EXTRACT(day FROM e."DateTime"), EXTRACT(month FROM e."DateTime"), 
       EXTRACT(year FROM e."DateTime"), sum("Energy")
   FROM "public"."EnergyWh" e
  GROUP BY 1,2,3

but following query should to work too:

SELECT e."DateTime"::date, sum("Energy")
   FROM "public"."EnergyWh" e
  GROUP BY 1

I am using a short syntax for GROUP BY ~ GROUP BY 1 .. group by first column.

Upvotes: 8

Ilesh Patel
Ilesh Patel

Reputation: 2155

Here is simple Example that can help you:

Table :

create table demo (value double precision);

Function

CREATE OR REPLACE FUNCTION sumvalues() RETURNS void AS
$BODY$
DECLARE
    inte text;
BEGIN
   FOR i IN 0..31 LOOP
    inte := 'INSERT INTO demo SELECT EXTRACT (DAY FROM TIMESTAMP ''01-01-2005 00:00:00''+ INTERVAL '''||i||' Days'')';
    EXECUTE inte;
   END LOOP;

END
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.sumvalues()
  OWNER TO postgres;

Function Call

SELECT sumvalues();

Output

 SELECT * FROM demo;

Here if you want to use some variable value into SQL query than you must have to use some DYNAMIC QUERY for that.

Reference : Dynamic query in pgsql

Upvotes: -2

Related Questions