[contractdashboard.git] / install.sql
--- a/install.sql
+++ b/install.sql
@@ -11,10 +11,15 @@
 LANGUAGE 'sql' IMMUTABLE;
  
 -- 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
   STYPE=anyarray,
   FINALFUNC=_final_mode, --Function to call after everything has been added to array
   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;