postrgresql row by row comparison of values in two columns

Question

I have a postgres db where after a GROUPBY I have to evaluate entries approximated by this table:

I then try to find the MIN and MAX of each column and count the number of rows where there isn't a zero in either column 'A' or 'B'. In this case I count one row because in row '4' there are non-zero values in both column 'A' and 'B'. Getting MIN and MAX is straightforward but I can't figure out how to do the last step.

SELECT MIN(A) as "minA",
       MAX(A) as "maxA",
       MIN(B) as "minB",
       MAX(B) as "maxB",
       COUNT(????) as "num_full"
FROM bigDB
GROUPBY inlet

I thought maybe I could do a sum on each row and test if the result was equal to the value of 'A' or 'B' i.e. if A or B is zero then the sum is A or B. But sum() works by column not row. Is there a way to do sums by row or is there a better way to do what I want to do?

Gordon Linoff · Accepted Answer · 2020-12-07 19:55:04Z

1

Use filter:

   COUNT(*) FILTER (WHERE A <> 0 AND B <> 0) as num_full

There is no need to enclose column aliases in double quotes.

answered Dec 7, 2020 at 19:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

postrgresql row by row comparison of values in two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related