Syed Asad Abbas Zaidi Syed Asad Abbas Zaidi - 26 days ago 11
SQL Question

Postgres: Group by clause with predefined set of values

I can do a query on the following table:

Table1:

id name source_url
1 object1 www.google.com
2 object2 www.facebook.com
3 object3 www.twitter.com
4 object5 www.google.com


Query:

select count(*) as counts, source_url from Table1 group by source_url


The above query will give me following result:

counts source_url
2 www.google.com
1 www.facebook.com
1 www.twitter.com


Now in the above scenario what I want is to group the table1 by my set of elements that I have in an array.
Example

arr[] = ["www.facebook.com","www.google.com","www.instagram.com","www.yahoo.com","www.abc.com"]


The result I want for the above table should be:

counts source_url
2 www.google.com
1 www.facebook.com
0 www.instagram.com
0 www.yahoo.com
0 www.abc.com

Answer

Unnest the array to a derived table, left join to it and use COALESCE() to replace NULL with 0:

SELECT COALESCE(counts, 0) AS counts, source_url
FROM   unnest('{www.facebook.com,www.google.com,www.instagram.com
               ,www.yahoo.com,www.abc.com}'::text[]) source_url
LEFT  JOIN (
   SELECT count(*) AS counts, source_url
   FROM   Table1
   GROUP  BY source_url
   ) USING (source_url);

source_url becomes the table and column name of the the derived table with this short syntax. You can be more verbose if you need to:

...
FROM   unnest(your_array) AS tbl_alias(column_alias)
...