MySQL – Search for column value based on another column value

3 Views

I have the following SQL statement to calculate several fields based on 3 WordPress tables. What I need is to calculate the SUM and AVG which are not working in the statement below.

I need to sum all meta_values ‘3279’ only when meta_key = image. The same applies to the AVG formula, ie, avg all meta_values when meta_key=odd.

Read More

Any help is much appreciated! 

SELECT (user_nicename) AS user,
       SELECT SUM(IF('meta_key' = 'image', 'meta_value' = '3279', 1,0)) AS won,
       SELECT SUM(IF('meta_key' = 'image', 'meta_value' = '3280', 1,0)) AS lost,
       COUNT('meta_key'='odd') AS total,
       FORMAT (AVG('meta_key' = 'odd', 'meta_value'),1) AS 'average odd',
       FORMAT (AVG ('meta_key' = 'bet', 'meta_value'),1) AS 'average bet',
       SUM('meta_key' = 'roi', 'meta_value') AS ROI
FROM wp_postmeta pm, wp_posts p
INNER JOIN wp_posts p ON pm.post_id = p.ID 
INNER JOIN wp_users u ON p.post_author = u.ID
WHERE Month(data) = MONTH(CURRENT_DATE)
GROUP BY (user_nicename)
ORDER BY ROI DESC
Post Views: 3

Related posts

Leave a Reply

1 comment

  1. There may be ways to simplify this in mysql. You might need to cast meta_value depending on what type it is, but to average on a subset of data, use null to ignore:

    AVG(Case When meta_key = 'odd' Then meta_value else Null End)