I am trying to recreate some excel formula in Netezza Aginity SQL so the processing can be done at the database level, but struggling with countifs.

The formula is roughly:

If( Countifs( policycolumn, policy, matchcolumn, "Match", codecolumn, code) >0, "true", "false")

So if there are any rows that match policy and "match" and code, itll be greater than 0 and be true. I am just struggling with the policy colimn part as the policy to count is the policy from that row.

Is there any way that people have used something to mimic countifs in sql?

Edit: To give some examples, my dataset looks like: (sorry i am bad with formatting):

policycolumn  |  matchcolumn  |  codecolumn

12345         | match         | c

12345         | no match      | d

9876          | match         | c

9876          | no match      | c

I would like to have an additional column that would show

policycolumn  |  matchcolumn  |  codecolumn | yesno

12345         | match         | c           | yes

12345         | no match      | d           | no

9876          | match         | c           | yes

9876          | match         | d           | no

Row 1 would be a yes because its counting the number of times 12345 appears, with a "match" and a "c". This row matches that so would be >0 and trigger the IF rule to be "yes"

ROw 2 wouldnt be a yes because its although its policy number 12345, its a "no match" and a "d".

Row 3 would be a yes because that row policy number 9876 is a "match" and a "c".

Row 4 isnt a yes because that row policy number 9876 is a "no match".

All criteria (Match Column = match and Codecolumn = c) have to be met for that row to be true, and the new column to set to "yes".

0 Answers