How to use count(‘condition’) over(partition by xx) in SQL

Here is the database:

The first table is named Trip and the second table is Users.

CLIENT_ID and DRIVER_ID are foreign keys for USERS_ID in the Users table. I want to find how many orders cancelled by the non-banned driver and non-banned passenger for each day (Trip.Status != 'completed' and Users.Banned ='No').

My code is :

SELECT
    t.Request_at AS 'Day',
    COUNT(Status != 'completed') OVER (PARTITION BY t.Request_at) AS 'Cancellation Num' 
FROM
    Trips t
JOIN 
    Users u1 ON t.Client_Id = u1.Users_Id AND u1.Banned = 'No'
JOIN 
    Users u2 ON t.Driver_Id = u2.Users_Id AND u2.Banned = 'No' 
WHERE
    t.Request_at >= '2013-10-01' AND t.Request_at <= '2013-10-03'
GROUP BY
    t.Request_at

The results for ‘2013-10-01’ and ‘2013-10-03’ are right(both equal to 1). But it turns wrong at ‘2013-10-02’. It becomes to 1, but it should be 0. I do not know where is the mistake in my code. Could someone help me?

Answer

I suspect that you really want conditional aggregation, not a window function:

SELECT t.Request_at AS Day,
       SUM(CASE WHEN Status <> 'completed' THEN 1 ELSE 0 END) as num_cancellations
FROM Trips t JOIN 
     Users u1
     ON t.Client_Id = u1.Users_Id AND
        u1.Banned = 'No' JOIN 
     Users u2
     ON t.Driver_Id = u2.Users_Id AND u2.Banned = 'No' 
WHERE t.Request_at >= '2013-10-01' AND t.Request_at <= '2013-10-03'
GROUP BY t.Request_at;

Note: Only use single quotes for string and date constants. Don’t use them for column aliases.