Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Postmeta meta_value sum

    Hy, everybody!

    The problem I have is with wordpress custom fields.

    I've got wp_postmeta table with post_id, meta_key and meta_value fields.
    I would like to echo a sum of meta_values of meta_key 'profit' but only between two dates that are stored as meta_values of meta_key 'start'. Those dates are stored in form of 20140715

    Someone please help!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    ??? If you want to use two dates (as you should, I would think), where do they come from?

    You say they are in the field meta_value where the meta_key field is 'start'.

    Why would there be multiple records with the meta_key 'start' and even if there are, how do you know which two records to pick?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    By the way, 20140715 is the standard way that MySQL stores dates, so that is no issue at all. Those are ready to use.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I really don't know how to explain this table so I made a screenshot
    Postmeta meta_value sum-question.jpg

  • #5
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is only one start for each post and only one profit for each post and they share the same post_id. I will enter the <em>between starts</em> manually into query.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    Ugh! What a TERRIBLE database design! Hard to think of a worse one!

    Let me think on this a bit. The query SHOULD be possible. Just not easy.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    Okay. Not as bad as I thought. I *think* this will do it. Give it a try.
    Code:
    SELECT SUM(CAST profit AS DECIMAL) AS totalProfit
    FROM wp_postmeta 
    WHERE meta_key = 'profit'
    AND post_id IN
        ( SELECT post_id FROM wp_postmeta 
          WHERE meta_key = 'start'
          AND CAST( meta_value AS DATE )
              BETWEEN '20140201' AND '20140228' )
        )
    Obviously you will change the two dates in the BETWEEN clause as needed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •