-- https://wiki.postgresql.org/wiki/Aggregate_Mode |
-- https://wiki.postgresql.org/wiki/Aggregate_Mode |
CREATE OR REPLACE FUNCTION _final_mode(anyarray) |
CREATE OR REPLACE FUNCTION _final_mode(anyarray) |
RETURNS anyelement AS |
RETURNS anyelement AS |
$BODY$ |
$BODY$ |
SELECT a |
SELECT a |
FROM unnest($1) a |
FROM unnest($1) a |
GROUP BY 1 |
GROUP BY 1 |
ORDER BY COUNT(1) DESC, 1 |
ORDER BY COUNT(1) DESC, 1 |
LIMIT 1; |
LIMIT 1; |
$BODY$ |
$BODY$ |
LANGUAGE 'sql' IMMUTABLE; |
LANGUAGE 'sql' IMMUTABLE; |
|
|
-- Tell Postgres how to use our aggregate |
-- Tell Postgres how to use our aggregate |
CREATE AGGREGATE mode(anyelement) ( |
CREATE AGGREGATE text_mode(anyelement) ( |
SFUNC=array_append, --Function to call for each row. Just builds the array |
SFUNC=array_append, --Function to call for each row. Just builds the array |
STYPE=anyarray, |
STYPE=anyarray, |
FINALFUNC=_final_mode, --Function to call after everything has been added to array |
FINALFUNC=_final_mode, --Function to call after everything has been added to array |
INITCOND='{}' --Initialize an empty array when starting |
INITCOND='{}' --Initialize an empty array when starting |
); |
); |
|
|
|
CREATE VIEW suppliers AS |
|
select distinct on ("supplierABN") "supplierABN", "supplierName", count("supplierName") from contractnotice |
|
where "supplierABN" is not null |
|
group by "supplierABN", "supplierName" |
|
order by "supplierABN" asc, count("supplierName") desc; |