Postgres: Group by clause with predefined set of values

Question

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 1

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)
...