I've got a sql related I've been trying to figure out.. There is a table of customer data with two types of interactions (dif columns) and corresponding date. I've pivoted the table so instead the data looks like the following:

etc .. What I am trying to do is count the number of occurrences where there is a 1 and 0 event on the same date, total for all customers. Maybe even counting within a date range would be useful.. Thanks!

CodePudding user response：

A general approach would be:

SELECT COUNT(*)
FROM
(
    SELECT date
    FROM yourTable
    WHERE event IN (0, 1)   -- can add date range here
    GROUP BY date
    HAVING MIN(event) <> MAX(event)
) t;

CodePudding user response：

You can use sum with group by:

with cte(date, pos, neg) as (
    select date, sum(event = 1), sum(event = 0) from events group by date
)
select * from cte where pos > 0 and neg > 0;

Edit: without a cte:

select * from (select date, sum(event = 1) pos, sum(event = 0) neg from events group by date) 
where pos > 0 and neg > 0;