CODEWITHSUNDEEP
sqlpostgresqlrow
MikeB2019x
MikeB2019x

Reputation: 1161

postrgresql row by row comparison of values in two columns

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

enter image description here

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?

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Use filter:

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

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

Upvotes: 1

Related Questions