CODEWITHSUNDEEP
mysqlsqldatetimegroup-by
Kavvson Empcraft
Kavvson Empcraft

Reputation: 443

Introducing a new column for previous month sum

I am trying to introduce a new column for my query, it currently counts sum of expenses in this month, the new column should display last months expences. I am not quite sure where to place it. 

SELECT cat.id_kat,
       cat.nazwa,
       coalesce(exp.tot, 0) AS PriceTotal
FROM wydatki_kategorie cat
LEFT JOIN
  (SELECT wydatki_wpisy.kategoria,
          sum(wydatki_wpisy.brutto) AS tot
   FROM wydatki_wpisy
   LEFT JOIN wydatki ON wydatki_wpisy.do_wydatku = wydatki.id_wydatku
   WHERE MONTH(wydatki.data_zakupu) = MONTH(CURRENT_DATE())
     AND wydatki.id_kupujacy = 1
   GROUP BY wydatki_wpisy.kategoria) exp ON cat.id_kat = exp.kategoria

Possibly might be needed ( if I'm not wrong ) - Where clause for the previous month.

 wydatki.data_zakupu >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) AND
 wydatki.data_zakupu<= DATE_SUB(NOW(), INTERVAL 1 MONTH)

SQL Fiddle example

Upvotes: 1

Views: 810

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Two things.

First, if you stop using WHERE MONTH(wydatki.data_zakupu) = MONTH(CURRENT_DATE()) to choose your dates you'll get three benefits.

  1. Your date searching will become sargable: an index will speed it up.
  2. You'll get a more general scheme for choosing months.
  3. If you have multiple years' worth of data in your tables, things will work better.

Instead, in general use this sort of expression to search for the present month. You already figured out most of this.

WHERE wydatki.data_zakupu >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
  AND wydatki.data_zakupu  < LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 0 MONTH

This looks for all datetime values on or after midnight at the first day of the present month, and before, but not on <, midnight at the first day of next month.

It generalizes to any month you want. For example, 

WHERE wydatki.data_zakupu >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 2 MONTH
  AND wydatki.data_zakupu  < LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH

gets you last month. This also works when the current month is January, and it works when you have multiple years' worth of data in your tables.

These expressions are a little verbose because MySQL doesn't have a FIRST_DAY(date) function, only a LAST_DAY(date) function. So we need all that + INTERVAL 1 DAY monkey business.

Second, pulling out a previous month's data is as simple as adding another LEFT JOIN ( SELECT... clause to your table, like so. (http://sqlfiddle.com/#!9/676df4/13)

SELECT ...
       coalesce(month1.tot, 0) AS LastMonth           
FROM wydatki_kategorie cat
LEFT JOIN 
      ...
LEFT JOIN
  (SELECT wydatki_wpisy.kategoria,
          sum(wydatki_wpisy.brutto) AS tot
   FROM wydatki_wpisy
   LEFT JOIN wydatki ON wydatki_wpisy.do_wydatku = wydatki.id_wydatku
   WHERE wydatki.data_zakupu >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 2 MONTH
     AND wydatki.data_zakupu  < LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
     AND wydatki.id_kupujacy = 1
   GROUP BY wydatki_wpisy.kategoria
  ) month1 ON cat.id_kat = month1.kategoria

As you can see, the date range WHERE clause here gets the previous month's rows.

Upvotes: 1

Related Questions