CODEWITHSUNDEEP
sqlpostgresql
malexanders
malexanders

Reputation: 3343

Calculate all days, group by week and include empty weeks?

I need a query that sums all the values for each day in a given week and groups by week including empty weeks.

This query groups by week and includes empty weeks but it isn't summing all days in the week as expected:

Expected Output: 

[ 
  ...
  {"week"=>"2019-02-28", "amount_net"=>"0"},
  {"week"=>"2019-03-07", "amount_net"=>"300"}
]

Actual Output: 

[
  ...
  {"week"=>"2019-02-28", "amount_net"=>"0"},
  {"week"=>"2019-03-07", "amount_net"=>"0"}
]

Here is the query I came up with:

SELECT 
  week, 
  COALESCE (amount_net, 0) as amount_net 
FROM 
  (
    SELECT 
      to_char(
        generate_series(
          timestamp '2018-12-13 22:34:31 UTC', 
          timestamp '2019-03-14', interval '1 week'
        ):: date, 
        'YYYY-MM-DD'
      ) as week
  ) d 
  LEFT JOIN (
    SELECT 
      to_char(
        date_trunc('week', created_at), 
        'YYYY-MM-DD'
      ) AS week, 
      SUM(
        ROUND(
          (
            coalesce(cost_items.base_price, 0) - coalesce(cost_items.base_discount, 0) + coalesce(cost_items.base_fee, 0) + coalesce(cost_items.base_taxes_total, 0) + coalesce(
              cost_items.base_commission_included, 
              0
            ) - coalesce(cost_items.base_voided_price, 0) + coalesce(
              cost_items.base_voided_discount, 
              0
            ) - coalesce(cost_items.base_voided_fee, 0) - coalesce(
              cost_items.base_voided_taxes_total, 
              0
            ) - coalesce(
              cost_items.base_voided_commission_included, 
              0
            )
          ):: numeric, 
          2
        )
      ) as amount_net 
    FROM 
      cost_items 
    WHERE 
      id IN ('0', '1', '2') 
    GROUP BY 
      1
  ) t USING (week) 
ORDER BY 
  week;

How do I adjust this query to properly sum all values for each day in the week?

Upvotes: 0

Views: 98

Answers (1)

malexanders
malexanders

Reputation: 3343

Figured it out:

with host_weeks as (
SELECT 
generate_series(
  timestamp '2018-12-01', 
  timestamp '2019-04-01', interval '1 day'
)::date as host_week )


select date_trunc('week', day)::date as week, sum(amount_net) from
(

select hw.host_week as day,
SUM(
  ROUND(
    (
      coalesce(ci.base_price, 0) - coalesce(ci.base_discount, 0) + coalesce(ci.base_fee, 0) + coalesce(ci.base_taxes_total, 0) + coalesce(
        ci.base_commission_included, 
        0
      ) - coalesce(ci.base_voided_price, 0) + coalesce(
        ci.base_voided_discount, 
        0
      ) - coalesce(ci.base_voided_fee, 0) - coalesce(
        ci.base_voided_taxes_total, 
        0
      ) - coalesce(
        ci.base_voided_commission_included, 
        0
      )
    ):: numeric, 
    2
  )
) as amount_net
from host_weeks hw left join cost_items ci on hw.host_week = ci.created_at::date and ci.id in (....)
group by 1 order by 1) t  group by 1 order by 1;

Upvotes: 0

Related Questions