I'm new with PostgreSQL and feeling quite overwhelmed..
What I'm trying to achieve is something like this:
|2019||Word word word||0.10|
|2018||Word word sad||0.80|
|2017||Word empty word||0.15|
|etc||Word word word||0.41|
Where percent is function_A divided by function_B** (function_A / function_B).
I already have the functions
- function_A has two parameters : year (extracted from status_date) and code. Returns the number of orders defined by the previous parameters.
- function_B has one parameter: year (extracted from status_date). Returns the total number of orders by year.
This is what, wrongly, I did so far - including code from nbk's original answer:
The function shouldn't have any parameters.
CREATE OR REPLACE FUNCTION summary_orders() RETURNS TABLE (retyear numeric, order_desc text, percentage bigint) AS $$ DECLARE retyear numeric DEFAULT 0; descrip text DEFAULT 0; percent bigint DEFAULT 0; BEGIN RETURN QUERY SELECT CAST(EXTRACT(year FROM created_dt) as numeric), order_desc as text, ROUND(function_A(inyear,incode))::decimal / function_B(inyear) as percent FROM orders o WHERE EXTRACT(YEAR FROM o.creation) = inyear AND o.order_code = incode AND o.descrip GROUP BY EXTRACT(year FROM o.creation), o.code ORDER BY retyear ASC; END; $$ LANGUAGE plpgsql;
To call the function I expect to use:
SELECT * from summary_code()
My data looks something like this:
CREATE TABLE orders ( id INT NOT NULL, code INT NOT NULL, service_id INT NOT NULL, descript CHARACTER VARYING(50), creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, status_date TIMESTAMP, created_user INT );
INSERT INTO orders (id, code, service_id, descript, creation, status_date, created_user) VALUES (100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1), (100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1), (100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1), (100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1), (100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1), (100,2394,558151,'Requested I'm tired','2019-06-16 11:12','2019-06-18 14:08',1);
I'm sorry if this comes as a little cheeky, but I don't know where to start.