CODEWITHSUNDEEP
sqlpostgresqljoingroup-bycount
Tom Tom
Tom Tom

Reputation: 352

Count multiple column without using many temporary tables then join PostgreSQL

I have data like this I have data like this

I want to count edit: distinct group1 and group 2 group by the Time and Type. I make each a temporary table then full outer join (on time and type) so desire column like:

Time    Type    Count_Group1     Count_Group2

Any shorter way to do this?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

This answers the original version of the question:

You can use a lateral join and aggregation:

select time, type, sum(in1), sum(in2)
from t cross join lateral
     (values (time1, group1, 1, 0), (time2, group2, 0, 1)
     ) v(time, grp, in1, in2)
group by time, type;

EDIT:

To count distinct values, use count(distinct):

select v.time, t.type, count(distinct t.group1), count(distinct t.group2)
from t cross join lateral
     (values (t.time1), (time2)
     ) v(time)
group by v.time, t.type;

Upvotes: 1

Related Questions