0

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
Ian-Fogelman
  • 1,595
  • 1
  • 9
  • 15
Jason Quick
  • 5
  • 3
  • Knowing the specific flavor of SQL being used would be useful as the functions required here would depend on that. My understanding following a cursory google of RedAsh is that it runs on top of many data sources, though I could be mistaken. – zzevannn Jun 29 '18 at 18:37
  • I believe it's t-sql – Jason Quick Jun 29 '18 at 19:01

2 Answers2

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)
MkGm7
  • 53
  • 5
  • 1
    I do not have access to year and month functions, if I used the second code I get this error "Error running query: column "view_cw_wc_revenue.invoice_date" must appear in the GROUP BY clause or be used in an aggregate function" – Jason Quick Jun 29 '18 at 19:11
  • That's because I forgot to change the field of the SELECT, I will edit it now. Try it again and tell me if it works please! – MkGm7 Jun 29 '18 at 19:13
0

You could try a ROLLUP. Sample illustration below:

Table data:

mysql> select * from wc_revenue;
+--------------+---------------+-------------+
| 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 |
| 2018-07-02   |        150.00 |      100.00 |
| 2018-07-05   |        350.00 |      250.00 |
| 2018-08-07   |        600.00 |      400.00 |
| 2018-08-09   |        900.00 |      600.00 |
+--------------+---------------+-------------+

mysql> SELECT month(invoice_date) as MTH, invoice_date, SUM(gross_revenue) AS gross_revenue, SUM(net_revenue) AS net_revenue 
FROM wc_revenue 
GROUP BY MTH, invoice_date WITH ROLLUP;
+------+--------------+---------------+-------------+
| MTH  | invoice_date | gross_revenue | net_revenue |
+------+--------------+---------------+-------------+
|    6 | 2018-06-22   |       1151.79 |      478.96 |
|    6 | 2018-06-25   |     475805.92 |   172193.58 |
|    6 | 2018-06-26   |       2518.74 |     1047.14 |
|    6 | 2018-06-27   |     112414.65 |    38108.13 |
|    6 | 2018-06-28   |       1623.99 |      659.72 |
|    6 | NULL         |     593515.09 |   212487.53 |
|    7 | 2018-07-02   |        150.00 |      100.00 |
|    7 | 2018-07-05   |        350.00 |      250.00 |
|    7 | NULL         |        500.00 |      350.00 |
|    8 | 2018-08-07   |        600.00 |      400.00 |
|    8 | 2018-08-09   |        900.00 |      600.00 |
|    8 | NULL         |       1500.00 |     1000.00 |
| NULL | NULL         |     595515.09 |   213837.53 |
+------+--------------+---------------+-------------+
Jagrut Sharma
  • 4,574
  • 3
  • 14
  • 19