CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Dakan_GIS
Dakan_GIS

Reputation: 81

PostgreSQL function to do calculation and then populate a table

I’m looking for some help with a process I am trying to do in pgAdmin 4. I have a table called ‘sales’. The attributes are; saleId (primary key), saleUnitprice (INT), saleQuantity (INT), saleTotal (INT), saleDate (DATE), prId (foreign key to products table), spId (foreign key to salesperson table)

I have a second table called ‘saletally’ which I am looking to populate with the total sales per month per salesperson.

The SQL to get the totals is

SELECT spid, 
       SUM(saletotal) 
FROM public."sales" 
WHERE EXTRACT(MONTH from saledate) = EXTRACT(MONTH from current_date) 
GROUP BY spid;

This return a number of records for each salesperson and their total sales for that month. I am trying to figure out how to put that info in a ‘saletally’ table.

I am looking to end up with 

 stID   stTotal   spID    stMonth  
------ --------- ------ ---------- 
 st1        800   sp1    January   
 st2        900   sp2    January   
 st3        900   sp3    January   
 st4        950   sp1    February  
 st5        800   sp2    February  
 st6        950   sp3    February

I’ve not done anything this complicated in SQL before and I think I need to do it as a function in PostgreSQL. Based on the syntax for a function here is what I’ve managed so far;

CREATE OR REPLACE FUNCTION totalSales ()
RETURNS integer AS $totalsales$
declare
stId_var varchar(4) := stId +1,
stTotal_var := SELECT SUM(saletotal) FROM public."sales" WHERE EXTRACT(MONTH from saledate) = EXTRACT(MONTH from current_date),
spId_var := SELECT spID FROM public.”sales”,
stMonth_var = EXRACT(MONTH from current_date);

BEGIN
Insert INTO public.”salestally”(stId, stTotal, spId, stMonth)
VALUES (stId_var, stTotal_var, spId_var, stMonth_var)
   RETURN totalsales;
END;
$totalsales$ LANGUAGE plpgsql;

I think there needs to be a loop in there somewhere. I’d be grateful if someone could help with this or can point me in the direction of a good tutorial. Thanks

Upvotes: 2

Views: 1812

Answers (1)

user330315
user330315

Reputation:

No function needed. If your SELECT statement returns the result you want to insert, you can use that directly as the source for the INSERT statement:

To get an incrementing column with your result, you can use the row_number() function. I would not store the st prefix - if that should be applied for all rows, it's simply a waste of space. You can easily concat that later in a view or in the select statement that you use to retrieve from the tally table:

insert INTO salestally(stId, stTotal, spId, stMonth)
SELECT row_number() over (order by extract(month from saledate)),
       SUM(saletotal), 
       spid, 
       extract(month from saledate)
FROM public.sales
GROUP BY spid, extract(month from saledate);

I would also not store the month as a name, but as a number. That way you can format latter when you display the month.

Upvotes: 3

Related Questions