SQL equivalent of countif() across 2 columns

Question

I am aware that there is a question with the same title as this one, but I'm not sure it addresses my question.

I have data that looks like this:

ID     Current    Nxt  
1      1A          1B
1      1B          1C
2      2A          2B
3      3A          3B
3      3B

Using countif in Excel '=COUNTIF($B$2:$B$6,C2)' I can get the data to look like this (formula is in occurred column):

ID     Current    Nxt  Occurred
1      1A          1B  1 
1      1B          1C  0 
2      2A          2B  0
3      3A          3B  1 
3      3B              0

Basically I'm just interested in indicating if the Nxt scheduled event has actually occurred (which is indicated if it exists in the data).

However, I would like to replicate this in my SQL query which looks like this:

SELECT
ID,
Current,
Nxt

FROM
Table

I think the answer could lie in doing something like:

sum(case when Current='1B' THEN 1 ELSE 0 END) over (partition by Current)

as suggested here: Sql Server equivalent of a COUNTIF aggregate function. The above works for one record, but the issue is that I need to make the criteria reference the contents of the Nxt column rather than have it hardcoded as the following doesn't work:

sum(case when Current=Nxt THEN 1 ELSE 0 END) over (partition by Current)

I guess this is because Current and Nxt never actually match on a row?

For reference my database uses Oracle 11.

Your database looks like a network database masquerading as a relational database. Are you stuck with this data structure? If so, you can expect to deal with all of the drawbacks of the network model of data while paying all of the costs associated with relational data. I saw data structures like this back in the 1980s when relational databases were the new kid on the block, but I haven't seen them for about 15 years. — Walter Mitty, Oct 03 '13 at 11:55
@WalterMitty - Sorry, I'm not sure I understand the question. I'm just a report writer, not a DB administrator. — bawpie, Oct 03 '13 at 12:08
Excuse me. If you are just a report writer, then you are stuck with the data structure. There are even some DBAs who are stuck with the data structure. After a data structure has been around for a while, there are a large number of business processes that are dependent on that structure, and changing the structure involves changing a lot of other things. Just ignore my comment. It won't help you with your problem. — Walter Mitty, Oct 03 '13 at 20:22

score 1 · Accepted Answer · answered Oct 03 '13 at 12:00

1

try this :

SELECT id
      ,current
      ,nxt
      ,nvl2(nxt,decode(next,lead(current) over (partition by id order by id),1,0),0) occured
  FROM yourtable

answered Oct 03 '13 at 12:00

Kobi

2,494
15
30

This seems to work, though I'm not quite sure that I understand how! I'm familiar with all of the functions you've used, but I would have never thought to use them (although I did think lead or lag would figure somewhere). Many thanks for your prompt response! – bawpie Oct 03 '13 at 12:10

score 1 · Answer 2 · answered Oct 03 '13 at 12:09

I would suggest you do the followng:

SELECT id, current, nxt
  FROM (SELECT id, current, nxt,
               CASE lead(current)
                       over (partition by id order by id)
                  WHEN nxt THEN 1
                  ELSE 0
               END AS occured
          FROM myTable)
 WHERE occured = 1;

EDIT :

Fiddle Demo

SQL equivalent of countif() across 2 columns

2 Answers2