Myujth

I came across a task where I have to return the total COUNT and SUM of issued policies for each day of the month and compare it to the previous year.

Table PolicyOrder has fields:

PolicyOrderId - primary key
CreatedAt (DATETIME)
CalculatedPremium - cost of policy or "premium"
PolicyOrderStatusId - irrelevant to the question but still - status of the policy.

To solve this I came up with a query that inner joins self table and sums/counts by grouping according to DAY of the creation date.

SELECT 
 DATE(po1.CreatedAt) AS dayDate_2017, 
 SUM(po1.CalculatedPremium) AS premiumSum_2017,
 COUNT(po1.PolicyOrderId) AS policyCount_2017,
 po2.*
FROM 
 PolicyOrder po1
INNER JOIN (
 SELECT 
 DATE(CreatedAt) AS dayDate_2018, 
 SUM(CalculatedPremium) AS premiumSum_2018, 
 COUNT(PolicyOrderId) AS policyCount_2018
 FROM 
 PolicyOrder po2
 WHERE
 YEAR(CreatedAt) = 2018 AND 
 MONTH(CreatedAt) = 10 AND
 PolicyOrderStatusId = 6 
 GROUP BY
 DAY(CreatedAt)
 ) po2 ON ( 
 DAY(po2.dayDate_2018) = DAY(po1.CreatedAt) 
 )
WHERE 
 YEAR(po1.CreatedAt) = 2017 AND 
 MONTH(po1.CreatedAt) = 10 AND 
 PolicyOrderStatusId = 6 
GROUP BY 
 DAY(po1.CreatedAt)

The above query returns these results:

dayDate_2017 | premiumSum_2017 | policyCount_2017 | dayDate_2018 | premiumSum_2018 | policyCount_2018
2017-10-01 | 4699.36 | 98 | 2018-10-01 | 8524.21 | 144
2017-10-02 | 9114.55 | 168 | 2018-10-02 | 7942.25 | 140
2017-10-03 | 9512.43 | 178 | 2018-10-03 | 9399.61 | 161
2017-10-04 | 9291.77 | 155 | 2018-10-04 | 6922.83 | 137
2017-10-05 | 8063.27 | 155 | 2018-10-05 | 9278.58 | 178
2017-10-06 | 9743.40 | 184 | 2018-10-06 | 6139.38 | 136
...
2017-10-31 | ...

The problem is that now I have to add two more columns in which policies has to be counted and amounts added from the start of the year UP UNTIL each returned row.

Desired results:
dayDate_2017 | premiumSum_2017 | policyCount_2017 | sumFromYearBegining | countFromYearBegining 
2017-10-01 | 4699.36 | 98 | 150000.34 | 5332 
2017-10-02 | 9114.55 | 168 | 156230.55 | 5443
2017-10-03 | 9512.43 | 178 | 160232.44 | 5663
 ...
2017-10-31 | ...


WHERE:

sumFromYearBegining (150000.34) - SUM of premiumSum from 2017-01-01 until 2017-10-01 (excluding)
countFromYearBegining (5332) - COUNT of policies from 2017-01-01 until 2017-10-01 (excluding)

sumFromYearBegining (1566239.55) - SUM of premiumSum from 2017-01-01 until 2017-10-02 (excluding)
countFromYearBegining (5443) - COUNT of policies from 2017-01-01 until 2017-10-02 (excluding)

sumFromYearBegining (160232.44) - SUM of premiumSum from 2017-01-01 until 2017-10-02 (excluding)
countFromYearBegining (5663) - COUNT of policies from 2017-01-01 until 2017-10-02 (excluding)

I have tried inner joining same table COUNTed and SUMed which failed because I cannot specify the range up to which I need to count and sum, I have tried LEFT joining and then counting, which fails because the results are counted not untill each row result but until the last result etc...

DB Fiddle: https://www.db-fiddle.com/f/ckM8HyTD6NjLbK41Mq1gct/5

Any help from you SQL ninjas highly appreciated.

We can use User-defined variables to calculate Rolling Sum / Count, in absence of Window Functions' availability.

We will first need to determine the Sum and Count for every day in the year 2017 (even though you need rows for a particular month only). Because, in order to calculate rolling Sum for the days in March month, we would need the sum/count values from the January, and February month(s) as well. One optimization possibility is that we can restrict calculations from the first month to the require month only.

Note that ORDER BY daydate_2017 is necessary in order to be able to calculate rolling sum correctly. By default, data is in unordered fashion. Without defining the order, we cannot guarantee that Sum will be correct.

Also, we need to two levels of sub-select queries. First level is used to calculate the Rolling sum values. Second level is used to restrict the result to February month only. Since WHERE is executed before SELECT; we cannot restrict the result to February month, in the first level itself.

If you need similar rolling Sum for the year 2018 as well; similar query logic can be implemented in other set of sub-select queries.

SELECT dt2_2017.*, dt_2018.*
FROM 
(
SELECT dt_2017.*,
 @totsum := @totsum + dt_2017.premiumsum_2017 AS sumFromYearBegining_2017,
 @totcount := @totcount + dt_2017.policycount_2017 AS countFromYearBeginning_2017
FROM (SELECT Date(po1.createdat) AS dayDate_2017,
 Sum(po1.calculatedpremium) AS premiumSum_2017,
 Count(po1.policyorderid) AS policyCount_2017
 FROM PolicyOrder AS po1
 WHERE po1.policyorderstatusid = 6 AND 
 YEAR(po1.createdat) = 2017 AND 
 MONTH(po1.createdat) <= 2 -- calculate upto February for 2017
 GROUP BY daydate_2017
 ORDER BY daydate_2017) AS dt_2017
CROSS JOIN (SELECT @totsum := 0, @totcount := 0) AS user_init_vars 
) AS dt2_2017 
INNER JOIN (
 SELECT 
 DATE(po2.CreatedAt) AS dayDate_2018, 
 SUM(po2.CalculatedPremium) AS premiumSum_2018, 
 COUNT(po2.PolicyOrderId) AS policyCount_2018
 FROM 
 PolicyOrder po2
 WHERE
 YEAR(po2.CreatedAt) = 2018 AND 
 MONTH(po2.CreatedAt) = 2 AND
 po2.PolicyOrderStatusId = 6 
 GROUP BY
 dayDate_2018
 ) dt_2018 ON DAY(dt_2018.dayDate_2018) = DAY(dt2_2017.dayDate_2017) 
WHERE YEAR(dt2_2017.daydate_2017) = 2017 AND 
 MONTH(dt2_2017.daydate_2017) = 2;

RESULT: View on DB Fiddle

| dayDate_2017 | premiumSum_2017 | policyCount_2017 | sumFromYearBegining_2017 | countFromYearBeginning_2017 | dayDate_2018 | premiumSum_2018 | policyCount_2018 |
| ------------ | --------------- | ---------------- | ------------------------ | --------------------------- | ------------ | --------------- | ---------------- |
| 2017-02-01 | 4131.16 | 131 | 118346.77 | 3627 | 2018-02-01 | 8323.91 | 149 |
| 2017-02-02 | 2712.74 | 85 | 121059.51000000001 | 3712 | 2018-02-02 | 9469.33 | 153 |
| 2017-02-03 | 3888.59 | 111 | 124948.1 | 3823 | 2018-02-03 | 6409.21 | 97 |
| 2017-02-04 | 2447.99 | 74 | 127396.09000000001 | 3897 | 2018-02-04 | 5693.69 | 120 |
| 2017-02-05 | 1437.5 | 45 | 128833.59000000001 | 3942 | 2018-02-05 | 8574.97 | 129 |
| 2017-02-06 | 4254.48 | 127 | 133088.07 | 4069 | 2018-02-06 | 8277.51 | 133 |
| 2017-02-07 | 4746.49 | 136 | 137834.56 | 4205 | 2018-02-07 | 9853.75 | 173 |
| 2017-02-08 | 3898.05 | 125 | 141732.61 | 4330 | 2018-02-08 | 9116.33 | 144 |
| 2017-02-09 | 8306.86 | 286 | 150039.46999999997 | 4616 | 2018-02-09 | 8818.32 | 166 |
| 2017-02-10 | 6740.99 | 204 | 156780.45999999996 | 4820 | 2018-02-10 | 7880.17 | 134 |
| 2017-02-11 | 4290.38 | 133 | 161070.83999999997 | 4953 | 2018-02-11 | 8394.15 | 180 |
| 2017-02-12 | 3687.58 | 122 | 164758.41999999995 | 5075 | 2018-02-12 | 10378.29 | 171 |
| 2017-02-13 | 4939.31 | 159 | 169697.72999999995 | 5234 | 2018-02-13 | 9383.15 | 160 |

If you want a way that avoids using @variables in the select list, and also avoids analytics (only mysql 8 supports them) you can do it with a semi-cartesian product:

WITH prevYr AS(
 SELECT 
 YEAR(CreatedAt) AS year_prev,
 MONTH(CreatedAt) AS month_prev,
 DAY(CreatedAt) AS day_prev,
 SUM(CalculatedPremium) AS premiumSum_prev, 
 COUNT(PolicyOrderId) AS policyCount_prev
 FROM 
 PolicyOrder
 WHERE
 CreatedAt BETWEEN '2017-02-01' AND '2017-02-28' AND
 PolicyOrderStatusId = 6 
 GROUP BY
 YEAR(CreatedAt), MONTH(CreatedAt), DAY(CreatedAt)
),
currYr AS (
 SELECT 
 YEAR(CreatedAt) AS year_curr,
 MONTH(CreatedAt) AS month_curr,
 DAY(CreatedAt) AS day_curr,
 SUM(CalculatedPremium) AS premiumSum_curr, 
 COUNT(PolicyOrderId) AS policyCount_curr
 FROM 
 PolicyOrder
 WHERE
 CreatedAt BETWEEN '2018-02-01' AND '2018-02-28' AND
 PolicyOrderStatusId = 6 
 GROUP BY
 YEAR(CreatedAt), MONTH(CreatedAt), DAY(CreatedAt)
) 


SELECT 
 *
FROM
 prevYr
 INNER JOIN 
 currYr
 ON 
 currYr.day_curr = prevYr.day_prev

 INNER JOIN
 (
 SELECT 
 main.day_prev AS dayRolling_prev, 
 SUM(pre.premiumSum_prev) AS premiumSumRolling_prev, 
 SUM(pre.policyCount_prev) AS policyCountRolling_prev
 FROM 
 prevYr main LEFT OUTER JOIN prevYr pre ON pre.day_prev < main.day_prev
 GROUP BY
 main.day_prev
 ) rollingPrev
 ON 
 currYr.day_curr = rollingPrev.dayRolling_prev

ORDER BY 1,2,3

We summarise the year 2017 and year 2018 data into two CTEs because it makes things a lot cleaner and neater later, particularly for this rolling count. You can probably follow the logic of the CTE easily because it's lifted more or less straight from your query - I only dropped the DATE column in favour of a year/month/date triplet because it made other things cleaner (joins) and can be recombined to a date if needed. I also swapped the WHERE clauses to use date BETWEEN x AND y because this will leverage an index on a column whereas using YEAR(date) = x AND MONTH(date) = y might not

The rolling counts works via something I referred to as a semi-cartesian. It's actually a cartesian product; any database join that results in rows from one o both tables multiplying and being represented repeatedly in the output, is a cartesian product. Rather than being a full product (every row crossed with every other row) in this case it uses a less than, so every row is only crossed with a subset of rows. As the date increases, more rows match the predicate, because a date of 30th has 29 rows that are less than it.

This thus causes the following pattern of data:

maindate predate maincount precount
2017-02-01 NULL 10 NULL

2017-02-02 2017-02-01 20 10

2017-02-03 2017-02-01 30 10
2017-02-03 2017-02-02 30 20

2017-02-04 2017-02-01 40 10
2017-02-04 2017-02-02 40 20
2017-02-04 2017-02-03 40 30

You can see that for any given main date, it repeats N - 1 times because there are N - 1 dates lower than in that satisfy the join condition predate < maindate

If we group by the maindate and sum the counts associated with each predate, we get the rolling sum of all the pre-counts on that main-date (So, on the 4th day of the month, it's SUM(pre count for dates 1st - 3rd, i.e. 10+20+30 = 60. On the 5th day, we sum the counts for days 1 to 4. On the 6th day, we sum days 1 to 5 etc)