SQL equivalent of COUNTIFs Excel formula?

Question

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".

Can you explain what you're trying to do (sample input data, sample output) in a way that doesn't involve Excel? — melpomene, May 31 '19 at 23:47
Not sure if I'm understanding the whole issue, but it sound like you just want to use `SUM` with a few conditions `SELECT SUM(CASE WHEN a=b AND c=d AND e=f THEN 1 ELSE 0 END) FROM mytable` — EdmCoff, May 31 '19 at 23:58
The above answer is probably as close as we will get unless you provide a full SET of input rows and desired output rows... — Lars G Olsen, Jun 01 '19 at 16:35
Thank you sorry I added in some additional information and an example of what I have put in and what I hope to get out, hope it helps. I am trying to match the logic that countif performs in Excel, by all conditions needing to be true for something to be counted once. Once all conditions are met and its counted once, the IF that wraps the COUNTIF is easy to replicate. — MikeLanglois, Jun 01 '19 at 19:50
In standard SQL you can use `count(*) filter (where ...)` - but I don't know if Netezza supports that — , Jun 02 '19 at 05:57

score 0 · Answer 1 · answered Jun 03 '19 at 23:08

This SQL should do what you are asking.

select policycolumn, matchcolumn, codecolumn, 
case when matchcolumn = 'match' and codecolumn = 'c' then 'yes' else 'no' end yesno
from <your table>

test result

with test_data_set as(
  select 12345 policycolumn, 'match' matchcolumn, 'c' codecolumn union all
  select 12345, 'no match', 'd' union all
  select 9876, 'match', 'c' union all
  select 9876, 'match', 'd')
select policycolumn, matchcolumn, codecolumn, 
case when matchcolumn = 'match' and codecolumn = 'c' then 'yes' else 'no' end yesno
from test_data_set;

*returns*
policycolumn matchcolumn codecolumn yesno
12345   match   c   yes
12345   no match    d   no
9876    match   c   yes
9876    match   d   no

Please let me know if it helped

SQL equivalent of COUNTIFs Excel formula?

1 Answers1