• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

PHP MySQL Query SUM of a field by date

I have a wordpress Website.
I want to be able to query the SUM of a meta_value field by DATE.  HOWEVER, I want to always query the same way, no matter what the date is. I always want it to be the prior month.
No matter what today is, I want to Query all of the previous month and none of the current month.

Does that make sense?
Something like the following?
$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
					FROM wp_woocommerce_order_itemmeta
					WHERE meta_key = '_line_total'
					AND meta_key = '_subscription_start_date > DATE_SUB( NOW(), INTERVAL 1 MONTH )'");

Open in new window

0
rgranlund
Asked:
rgranlund
  • 3
1 Solution
 
Beverley PortlockCommented:
Without having actual data to test with, it is a bit tricky but this seemed to work. I have altered it to work with your field names and tablenames and hopefully it survived the process

SELECT
     YEAR( _subscription_start_date ),
     MONTH( _subscription_start_date ),
     SUM(meta_value) as summaryCount

FROM
     wp_woocommerce_order_itemmeta

WHERE
     _subscription_start_date < DATE_FORMAT( NOW(), '%Y-%m-00' ) AND
     meta_key = '_line_total'

GROUP BY
     YEAR( _subscription_start_date ) DESC,
     MONTH( _subscription_start_date ) DESC

ORDER BY
     YEAR( _subscription_start_date ) DESC,
     MONTH( _subscription_start_date ) DESC

LIMIT 1

Open in new window

0
 
rgranlundAuthor Commented:
@beverly, I needed to change my question a little.  Maybe this makes more sense:
$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
					FROM wp_woocommerce_order_itemmeta
					WHERE meta_key = '_line_total'
					AND meta_key = '_subscription_start_date' >= :first_day
					AND meta_key = '_subscription_start_date' <= :end_day");

Open in new window

0
 
Beverley PortlockCommented:
OK, I will have a look, but I notice that you have $first_day and $last_day but your query has first_day and end_day. Is that a typo?
0
 
Beverley PortlockCommented:
OK... what about

$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
                         FROM wp_woocommerce_order_itemmeta
                         WHERE meta_key = '_line_total'
                         AND '_subscription_start_date' >= ':first_day'
                         AND '_subscription_start_date' <= ':last_day' ");
                                          

Open in new window


I have removed the meta_key from the 2nd and 3rd subclauses because it makes no sense to have it there with a different value than the first subclause. That just guarantees and empty result as well as being syntactically wrong.

I have also enclosed the :first_day and :last_day variables in single quotes. I presume that the colons are part of PDO's syntax? I would have encoded it with $ signs as follows

$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
                         FROM wp_woocommerce_order_itemmeta
                         WHERE meta_key = '_line_total'
                         AND '_subscription_start_date' >= '$first_day'
                         AND '_subscription_start_date' <= '$last_day' ");
                                          

Open in new window

0
 
PortletPaulCommented:
without the benefit of data, or actual values in those date variables, I would just caution you about using >= with <=

That is the same as using between i.e.

                    select * from x where dt >= '2014-01-01' and dt <= '2014-12-31'

is exactly the same as using:

                    select * from x where dt between '2014-01-01' and  '2014-12-31'


But the most reliable method of filtering for a date range is to use >= with < like this:

                    select * from x where dt >= '2014-01-01' and dt < '2015-01-01'

see: "Beware of Between"
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now