Get SUM of Current Week data and Current Year data from SQLite

Question

I have a SQLite database and sales table is like the following,

| Id | quantity |     dateTime     |
------------------------------------
| 1  | 10       | 2019-12-25 12:55 |
| 2  | 05       | 2019-12-30 12:55 |
| 3  | 25       | 2020-08-23 12:55 |
| 4  | 25       | 2020-08-24 12:55 |
| 5  | 56       | 2020-08-25 12:55 |
| 6  | 25       | 2020-08-26 12:55 |
| 7  | 12       | 2020-08-27 12:55 |
| 8  | 30       | 2020-08-28 12:55 |
| 9  | 40       | 2020-08-29 12:55 |

I need to get the Current Week data (Mon to Sun) and the Current Year data from (Jan to Dec). So if I pass today date I need to get only the Current Week sales data group by days like the following,

If I pass today date and time (2020-08-28 13:55) the query should give me Current Week data like this,

Day        Sold Items (SUM(quantity))
Monday     20
Tuesday    25
Wednesday  10
Thursday   50
Friday     60
Saturday   0 (If the date hasn't come yet I need to get 0)
Sunday     0

And same as the Current Year data when I pass the Current Date,

Month      Sold Items (SUM(quantity))
JAN        20
FEB        25
MAR        10
APR        50
MAY        60
JUN        0 (If the month hasn't come yet I need to get 0)
JUL        0
...        ...

I tried with multiple queries in SQLite but couldn't get what I need. Here are the queries I tried,

Weekly Data (This one gave me past week data also)
SELECT SUM(quantity) as  quantity, strftime('%w', dateTime) as Day
From sales
Group by strftime('%w', dateTime)

Monthly Data
SELECT SUM(quantity) as  quantity, strftime('%m', dateTime) as Month
From sales
Group by strftime('%m', dateTime)

So anybody can help me to achieve this? Thanks in advance.

Answer 1

For the totals of the current week you need a CTE that returns the names of the days and the another one that returns the Monday of the current week.
You must cross join these CTEs and left join your table to aggregate:

with 
  days as (
    select 1 nr, 'Monday' day union all
    select 2, 'Tuesday' union all
    select 3, 'Wednesday' union all
    select 4, 'Thursday' union all
    select 5, 'Friday' union all
    select 6, 'Saturday' union all
    select 7, 'Sunday'
  ),
  weekMonday as (
    select date(
        'now', 
        case when strftime('%w', 'now') <> '1' then '-7 day' else '0 day' end, 
        'weekday 1'
      ) monday
  )
select d.day, 
       coalesce(sum(t.quantity), 0) [Sold Items]
from days d cross join weekMonday wm
left join tablename t
on strftime('%w', t.dateTime) + 0 = d.nr % 7
and date(t.dateTime) between wm.monday and date(wm.monday, '6 day')
group by d.nr, d.day
order by d.nr

For the totals of the current year you need a CTE that returns the month names and then left join the table to aggregate:

with 
  months as (
    select 1 nr, 'JAN' month union all
    select 2 nr, 'FEB' union all
    select 3 nr, 'MAR' union all
    select 4 nr, 'APR' union all
    select 5 nr, 'MAY' union all
    select 6 nr, 'JUN' union all
    select 7 nr, 'JUL' union all
    select 8 nr, 'AUG' union all
    select 9 nr, 'SEP' union all
    select 10 nr, 'OCT' union all
    select 11 nr, 'NOV' union all
    select 12 nr, 'DEC'
  )
select m.month, 
       coalesce(sum(t.quantity), 0) [Sold Items]
from months m
left join tablename t
on strftime('%m', t.dateTime) + 0 = m.nr
and date(t.dateTime) between date('now','start of year') and date('now','start of year', '1 year', '-1 day')
group by m.nr, m.month
order by m.nr