Count multiple column without using many temporary tables then join PostgreSQL

Question

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?

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Sep 1, 2020 at 16:26
@a_horse_with_no_name thank you, because im using mobile to write this questions — Tom Tom
– Tom Tom, Commented Sep 1, 2020 at 16:32
Before anything, consider re-factoring your table design for a normalized framework. You avoid complex querying in doing so. Columns and tables with numbered suffixes are usually sub-optimal schema. Aim for long not wide tables. — Parfait
– Parfait, Commented Sep 1, 2020 at 16:48

Gordon Linoff · Accepted Answer · 2020-09-03 11:24:01Z

1

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;

edited Sep 3, 2020 at 11:24

answered Sep 1, 2020 at 16:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

9 Comments

Tom Tom Over a year ago

I want each column for group 1 and group 2 count. Why there is a grp column? @gordon

Gordon Linoff Over a year ago

@HaoHao . . . Good point. grp is not needed. I removed it.

Tom Tom Over a year ago

there is still 'grp' above, I replace that with 'type' my code say type is ambiguous, I didn't know about this type of join yet. @gordon

Tom Tom Over a year ago

what is in1 and in2?

Tom Tom Over a year ago

1,0 and 0,1 also I hope you could explain more @gordon

|

Collectives™ on Stack Overflow

Count multiple column without using many temporary tables then join PostgreSQL

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related