COUNT of GROUP of two fields in SQL Query -- Postgres

Question

I have a table in postgres with 2 fields: they are columns of ids of users who have looked at some data, under two conditions:

viewee  viewer
------  ------
93024   66994
93156   93151
93163   113671
137340  93161
92992   93161
93161   93135
93156   93024

And I want to group them by both viewee and viewer field, and count the number of occurrences, and return that count from high to low:

id       count
------   -----
93161    3
93156    2
93024    2
137340   1
66994    1
92992    1
93135    1
93151    1
93163    1

I have been running two queries, one for each column, and then combining the results in my JavaScript application code. My query for one field is...

SELECT "viewer",
    COUNT("viewer")
        FROM "public"."friend_currentfriend"
            GROUP BY "viewer"
            ORDER BY  count DESC;

How would I rewrite this query to handle both fields at once?

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-01-17 19:21:12Z

1

You can combine to columns from the table into a single one by using union all then use group by as below:

select id ,count(*) Count from (
select viewee id from vv
union all
select viewer id from vv) t
group by id
order by count(*) desc

Results:

edited Jan 17, 2021 at 19:21

answered Jan 17, 2021 at 19:14

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-01-17 19:31:22Z

1

This is a good place to use a lateral join:

select v.viewx, count(*)
from t cross join lateral
     (values (t.viewee), (t.viewer)) v(viewx)
group by v.viewx
order by count(*) desc;

answered Jan 17, 2021 at 19:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Satwik Nadkarny · Accepted Answer · 2021-01-17 19:17:21Z

0

You can try this :

SELECT      a.ID,
            SUM(a.Total) as Total
FROM        (SELECT     t.Viewee AS ID,
                        COUNT(t.Viewee) AS Total
             FROM       #Temp t
             GROUP BY   t.Viewee
             UNION
             SELECT     t.Viewer AS ID,
                        COUNT(t.Viewer) AS Total
             FROM       #Temp t
             GROUP BY   t.Viewer
            ) a 
GROUP BY    a.ID
ORDER BY    SUM(a.Total) DESC

answered Jan 17, 2021 at 19:17

Satwik Nadkarny

5,1432 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

COUNT of GROUP of two fields in SQL Query -- Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related