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

Is that a regular PostgreSQl array or a JSON array? – Patrick Nov 12 '16 at 04:20 — Patrick, Nov 12 '16 at 04:20

ScanQR · Answer 1 · 2016-11-12T04:44:15.237

0

You need to apply IN operator with your dataset in your existing query.

select count(*) as counts, source_url from Table1 WHERE source_url IN ('www.facebook.com','www.google.com','www.instagram.com','www.yahoo.com','www.abc.com') group by source_url

EDIT 2 : If you need count for those rows which doesn't match your dataset then you can try built in SQL functions. I have mentioned one of them. Based on your database you can find available functions.

select COALESCE(count(*), 0) as counts, source_url from Table1 WHERE source_url IN ('www.facebook.com','www.google.com','www.instagram.com','www.yahoo.com','www.abc.com') group by source_url
UNION
select COALESCE(count(*), 0) as counts, source_url from Table1 WHERE source_url NOT IN ('www.facebook.com','www.google.com','www.instagram.com','www.yahoo.com','www.abc.com') group by source_url

edited Nov 12 '16 at 04:44

answered Nov 12 '16 at 04:06

ScanQR

3,740
1
13
30

I already tried the above query. It won't give the result for the source_url that is not present in the table. It just gives the count for source_url that is available in the table – Syed Asad Abbas Zaidi Nov 12 '16 at 04:09
queried the updated answer in my database... It still gives the same result. Do not give the result for url's that have 0 occurrence – Syed Asad Abbas Zaidi Nov 12 '16 at 04:29
@SyedAsadAbbasZaidi in that case you need do UNION of results. Because you have a condition to be met and also want result set which don't meet the condition. I have add UNION solution. – ScanQR Nov 12 '16 at 04:45

Sergey Gornostaev · Answer 2 · 2016-11-12T05:31:50.010

0

select source_url, count(id)
from (
    select * from unnest(arr) as source_url
) as t2
left join lateral (
    select source_url, id from Table1 where source_url = any(arr)
) as t1
using(source_url) group by source_url;

edited Nov 12 '16 at 05:31

answered Nov 12 '16 at 04:22

Sergey Gornostaev

7,596
3
27
39

score 0 · Accepted Answer · answered Nov 12 '16 at 05:13

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

Erwin : http://stackoverflow.com/questions/41034036/postgresql-sql-view-complex-structure — Syed Asad Abbas Zaidi, Dec 08 '16 at 09:56

Postgres: Group by clause with predefined set of values

3 Answers3