How can I group into months a column count?-CodePudding

having an issue here on how do I print the count of each signed contracts per month. This is my code:

SELECT COUNT(A.CONTRACT_NUMBER) AS count

FROM DM_SALES.V_SALES_DM_DATA A
  LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 

WHERE 1=1
  AND VSR.NAME_PRODUCER LIKE '%OPPO%'
  AND A.CONTRACT_STATE <> 'Cancelled' 
  AND a.cnt_signed=1 
  AND A.LOAN_TYPE = 'Consumer Loan'
  AND A.DTIME_SIGNATURE >= '01-01-2022'

GROUP BY (A.DTIME_SIGNATURE, 'yyyy-mm') 
;

But the results are:

I'd like to print it out just like this

Months | Count

2022-01 | 10000

2022-02 | 12000

Thanks!

CodePudding user response：

Include month into the select column list.

Moreover, you'll have to fix group by clause (you're missing to_char function) and date value (I presume dtime_signature is a DATE datatype; use date literal or to_date function with appropriate format model). Changes are indicated in code that follows:

  SELECT TO_CHAR (a.dtime_signature, 'yyyy-mm') AS months,     --> here
         COUNT (a.contract_number) AS COUNT
    FROM dm_sales.v_sales_dm_data a
         LEFT JOIN dm_sales.v_sales_dm_contract_bundle vsr
            ON a.contract_number = vsr.contract_number
   WHERE     1 = 1
         AND vsr.name_producer LIKE '%OPPO%'
         AND a.contract_state <> 'Cancelled'
         AND a.cnt_signed = 1
         AND a.loan_type = 'Consumer Loan'
         AND a.dtime_signature >= DATE '2022-01-01'            --> here
GROUP BY TO_CHAR (a.dtime_signature, 'yyyy-mm');               --> here