1 Replies - 279 Views - Last Post: 12 July 2017 - 12:48 PM

#1 anon_guy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 17-April 17

MySQL query for this time last year

Posted 12 July 2017 - 12:27 PM

I'm confused about these php mysql statements.

I need to get four different results from the same column(amount) in a table.

The functions find the sum of (amount) for the following:
Year to date
Giving time last year
Total giving last year
Giving in the last 30 days

They all seem to be variations of the same. I don't know how to get LastYearNow to show the correct results. How do I show results from the beginning of 2016 to today's date 2016?

public function getSum() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 12 MONTH) ");           
       return $query->row; 
}
    public function lastThirty() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 1 MONTH)" );            
       return $query->row; 
}
public function lastYearNow() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 12 MONTH) ");           
       return $query->row; 
}
    public function lastYearTotal() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where YEAR(date_added) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))" );           
       return $query->row; 
}


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL query for this time last year

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13556
  • View blog
  • Posts: 54,098
  • Joined: 12-June 08

Re: MySQL query for this time last year

Posted 12 July 2017 - 12:48 PM

Why not use greater than and the datetime-add?

Off the top of my head:
SELECT *
from WHERE_EVER
WHERE date_entered > date_add(getdate(), -1 year)


so.. get any data that is greater than one year ago from right now.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1