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

LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.