Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

PHP MySQL Query SUM of a field by date

Posted on 2014-10-15
5
525 Views
Last Modified: 2014-10-15
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
Comment
Question by:rgranlund
  • 3
5 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40382743
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
 
LVL 7

Author Comment

by:rgranlund
ID: 40382756
@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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40382768
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
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 40382793
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40383320
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to count occurrences of each item in an array.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question