Conditional return of aggregated data

From: Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>
To: postgre <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Conditional return of aggregated data
Date: 2019-12-02 11:10:44
Message-ID: huts4iYHSGpV-pwvHgoN-X8_PwFIFr-j7wbWueEXrs2Du5bKHLE6lik-8y-s6DBKkhAZr-gGGdbadVf3g9uHTf3CB5qX4SJDfD8ezTKLfM8=@protonmail.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have some aggregated statistics which are currently being queried as follows:

create view stats_age as
SELECT a.category,
a.statcount,
b.a_desc
FROM reg_aggregated_stats a,regs_select_age b where a.category='age' and a.datapoint::smallint=b.a_val order by a.datapoint asc;

However, as these relate to event registrations, a suggestion has been made that the database really should be returning nothing until a certain number of registrations has been met (in order to avoid privacy infringing inferrence from what should be an otherwise fully irreversibly aggregated dataset).

Now, the queries are wrapped in PL/PGSQL functions anyway, so I could make a second call to Postgres to find out sum(statcount) and then conditionally return based on that.

But is there a smarter way to do this out of a single SQL query ?

My initial idea was something along the lines of :
select (select sum(statcount) from stats_residence) as aggstat,statcount,short_name_en from stats_residence where aggstat>some_number;

But as I soon discovered that's not valid syntax! Hence ideas welcome from those smarter than me.

Thanks !

Laura

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wim Bertels 2019-12-02 11:27:42 Re: Conditional return of aggregated data
Previous Message Ajay Pratap 2019-12-02 06:52:13 pgbackrest concerns and doubts.