SQLize Online / PHPize Online  /  SQLtest Online

create table if not exists TimeTracking ( ID serial primary key not null, Peer varchar not null , Date date not null , Time time not null , State integer check ( State in(1, 2) ) ); create table if not exists Peers ( Nickname varchar primary key, Birthday date ); INSERT INTO Peers(Nickname, Birthday) VALUES ('imagnifi', '1979-01-26'), ('pfidelia', '2003-11-06'), ('victoriv', '1994-12-04'), ('lcoon', '2003-03-02'), ('vluann', '2009-01-01'), ('hspeaker', '2009-02-03'); INSERT INTO TimeTracking(Peer, Date, Time, State) VALUES ('victoriv', '2022-11-15', '08:02:03', 1), ('pfidelia', '2022-11-15', '9:13:03', 1), ('pfidelia', '2022-11-15', '12:03:03', 2), ('hspeaker', '2010-11-15', '14:03:04', 1), ('vluann', '2022-11-15', '20:06:30', 1), ('vluann', '2022-11-15', '23:06:30', 2), ('pfidelia', '2022-11-15', '19:03:03', 1), ('pfidelia', '2022-11-15', '21:03:03', 2), ('victoriv', '2022-11-15', '20:05:03', 2), ('victoriv', '2019-04-18', '21:03:03', 1), ('victoriv', '2019-04-18', '21:53:03', 2), ('imagnifi', '2019-01-18', '03:03:03', 1), ('imagnifi', '2019-01-18', '11:53:03', 2), ('victoriv', '2019-11-18', '11:03:03', 1), ('victoriv', '2019-11-18', '21:33:03', 2); CREATE OR REPLACE FUNCTION f_array() RETURNS TABLE(month integer, count integer) AS $$ DECLARE months integer ARRAY [0]; names integer ARRAY [0]; BEGIN FOR i IN 0..11 LOOP months[i] = (SELECT count(*) FROM (SELECT * FROM timetracking JOIN peers ON timetracking.peer = peers.nickname WHERE date_part('month', timetracking.date) = date_part('month', peers.birthday) AND timetracking.state = 1) AS gg WHERE date_part('month', gg.date) = i + 1); names[i] = i + 1; END LOOP; RETURN QUERY SELECT unnest(names) AS first, unnest(months) AS second; END; $$ LANGUAGE plpgsql; SELECT gg.month, gg.count FROM f_array() AS gg; SELECT (SELECT TO_CHAR(TO_DATE(generate_series(1,12)::text,'MM'), 'Month')), (SELECT gg.count FROM f_array() AS gg)