Aggregate data from days into a month

Question

I have data that is presented by the day and I want to the data into a monthly report. The data looks like this.

INVOICE_DATE GROSS_REVENUE NET_REVENUE

2018-06-28 ,1623.99 ,659.72

2018-06-27 ,112414.65 ,38108.13

2018-06-26 ,2518.74 ,1047.14

2018-06-25 ,475805.92 ,172193.58

2018-06-22 ,1151.79 ,478.96

How do I go about creating a report where it gives me the total gross revenue and net revenue for the month of June, July, August etc where the data is reported by the day?

So far this is what I have

SELECT invoice_date, 
SUM(gross_revenue) AS gross_revenue, 
SUM(net_revenue) AS net_revenue
FROM wc_revenue
GROUP BY invoice_date

Answer 1

I would simply group by year and month.

SELECT invoice_date, 
       SUM(gross_revenue) AS gross_revenue, 
       SUM(net_revenue) AS net_revenue 
FROM wc_revenue GROUP BY year(invoice_date), month(invoice_date)

Since I don't know if you have access to the year and month functions, another solution would be to cast the date as a varchar and group by the left-most 7 characters (year+month)

SELECT left(cast(invoice_date as varchar(50)),7) AS invoice_date, 
       SUM(gross_revenue) AS gross_revenue, 
       SUM(net_revenue) AS net_revenue 
FROM wc_revenue GROUP BY left(cast(invoice_date as varchar(50)),7)