I am trying to create an SQL query table with the aim of producing the attached stacked bar chart in Zoho Analytics.

I can count the number of S1 payments as follows:

SELECT

abs_month("S1 invoice received"),

count(abs_month("S1 invoice received")) AS 'S1 Payments'

FROM  "Casework (Zoho CRM)"

GROUP BY  abs_month("S1 invoice received")

 

And similarly the number of S2 payments as follows:

SELECT

abs_month("S1 invoice received"),

count(abs_month("S2 invoice received")) AS 'S2 Payments'

FROM  "Casework (Zoho CRM)"

GROUP BY  abs_month("S2 invoice received")

 

But how do I create one table showing a count of the number of S1  and S2 payments received in any given month?

Is this possible in one table or do I need to count the values separately and join two tables together where the months and years match?

I would also like to order this information by year and month, which I don’t think is possible which the code above as I believe the dates appear as text.

Thanks, Scott