I'm new with PostgreSQL and feeling quite overwhelmed..
What I'm trying to achieve is something like this:
Wanted outcome
year | descrip | percent |
---|---|---|
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.
Function
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:
'orders' Table
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
);
Records
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.