Query to return month-wise count in PostgreSQL

Question

I am trying to write a query in PostgreSQL that should return "month" wise results when two dates are given.

  SELECT count(overall_sl) as total_sales, count(CASE WHEN overall_sl < value_1 THEN 1 END) failed_sales
    FROM  (
                SELECT overall_sl, value_1
                FROM   salecombined c where c.date_updated between '2018-01-01' and '2018-12-31'
                 GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
                value_1, category_id, subcategory_id, branch_name
     ) sales; 

I expect results to be like this (The above query doesn't do that)

start_dt    end_dt       total_sales   failed_sales
--------    ------       -----------   -------------
2018-01-01  2018-01-31   0              0
2018-02-01  2018-02-28   589            154
2018-03-01  2018-03-31   107            14
2018-04-01  2018-04-30   375            114
-- and so on

I wrote below query but it is taking longer to execute; how can I optimise this?

SELECT  date_trunc('month', dd):: date start_dt,
(date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date as end_dt, 

    (select count(overall_sl) from (
            SELECT overall_sl
        FROM   salecombined c                
        WHERE c.date_updated between date_trunc('month', dd):: date and (date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date
        GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
        category_id, subcategory_id, branch_name
         ) jobs
     ) total_sales,

     (select count(CASE WHEN overall_sl < value_1 THEN 1 END) from (
            SELECT overall_sl, value_1
        FROM   salecombined c                
        WHERE c.date_updated between date_trunc('month', dd):: date and (date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date
        GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
        value_1, category_id, subcategory_id, branch_name
         ) jobs
     ) failed_sales

FROM generate_series
    ( '2018-01-01'::timestamp 
    , '2018-12-11'::timestamp
    , '1 month'::interval) dd

I am not expert in SQL or PostgreSQL.

Answer 1

I am not quiet sure but maybe you are searching for GROUP BY date_trunc():

demo:db<>fiddle

SELECT 
    date_trunc('month', sdate)::date as month_begin,
    (date_trunc('month', sdate) + interval '1 month -1 day')::date as month_end,
    SUM(value)
FROM sales    
WHERE sdate BETWEEN <start> and <end>
GROUP BY date_trunc('month', sdate)

1. date_trunc('month', date) converts the date to the first of the month. So you can group all dates within one month (because all dates are the first of a month in that group)
2. date_trunc('month', sdate) + interval '1 month -1 day' calculates the last day of a month (date_trunc to go to first, add one month to go the first of the next month, subtract one day to get to the day before the next first = last day of current month.)