SQLite - Use a CTE to divide a query

Question

quick question for those SQL experts out there. I feel a bit stupid because I have the feeling I am close to reaching the solution but have not been able to do so.

If I have these two tables, how can I use the former one to divide a column of the second one?

WITH month_usage AS 
(SELECT strftime('%m', starttime) AS month, SUM(slots) AS total
 FROM Bookings
 GROUP BY month)

SELECT strftime('%m', b.starttime) AS month, f.name, SUM(slots) AS usage
FROM Bookings as b 
LEFT JOIN Facilities as f
ON b.facid = f.facid
GROUP BY name, month
ORDER BY month

The first one computes the total for each month

The second one is the one I want to divide the usage column by the total of each month to get the percentage

When I JOIN both tables using month as an id it messes up the content, any suggestion?

Answer 1

I want to divide the usage column by the total of each month to get the percentage

Just use window functions:

SELECT 
    strftime('%m', b.starttime) AS month, 
    f.name, 
    SUM(slots) AS usage
    1.0 * SUM(slots) AS usage 
        / SUM(SUM(slots)) OVER(PARTITION BY strftime('%m', b.starttime)) ratio
FROM Bookings as b 
LEFT JOIN Facilities as f
ON b.facid = f.facid
GROUP BY name, month
ORDER BY month