Aggregates in PostgreSQL

Question

Write an aggregate to count the number of times the number 40 is seen in a column.

Use your aggregate to count the number of 40 year olds in the directory table.

This is what I was doing:

Create function aggstep(curr int) returns int as $$
begin
    return curr.count where age = 40;
end;
$$ language plpgsql;

Create aggregate aggs(integer) (
stype = int,
initcond = '',
sfunc = aggstep);

Select cas(age) from directory;

Laurenz Albe · Answer 1 · 2018-12-04T21:21:21.160

0

You could do it for example like this:

First, create a transition function:

CREATE FUNCTION count40func(bigint, integer) RETURNS bigint
   LANGUAGE sql IMMUTABLE CALLED ON NULL INPUT AS
'SELECT $1 + ($2 IS NOT DISTINCT FROM 40)::integer::bigint';

That works because FALSE::integer is 0 and TRUE::integer is 1.

I use IS NOT DISTINCT FROM rather than = so that it does the correct thing for NULLs.

The aggregate can then be defined as

CREATE AGGREGATE count40(integer) (
   SFUNC = count40func,
   STYPE = bigint,
   INITCOND = 0
);

You can then query like

SELECT count40(age) FROM directory;

edited Dec 04 '18 at 21:21

answered Dec 04 '18 at 20:02

Laurenz Albe

209,280
17
206
263

That's surprising - unless I'm completely wrong, this should solve your homework question. – Laurenz Albe Dec 04 '18 at 20:33
It actually asks to create aggregate and use it to find number of 40 year olds from table..considering we have table with column age – Sean Dec 04 '18 at 21:18
Oh, I thought that was obvious - I have added a line to the answer. – Laurenz Albe Dec 04 '18 at 21:21
thank you very much, actually I looked back into your solution and it did work, really appreciate your help. Ok, I will start looking into documentation again, I have already gone through it but now will pay more attention – Sean Dec 05 '18 at 00:18
I am glad to hear that - I thought it could only be a misunderstanding. – Laurenz Albe Dec 05 '18 at 03:59
could you please tell me why do we enter bigint and int here: CREATE FUNCTION count40func(bigint, integer) RETURNS bigint while input is age which is int? What is the significance? It will really help me understand it once for all – Sean Dec 06 '18 at 09:09
That is just in expectation that counts can potentially be very large. `count` returns `bigint` too. But for the example here you can use `integer` just as well. – Laurenz Albe Dec 06 '18 at 11:04

Aggregates in PostgreSQL

1 Answers1