[SOLVED] SUM() function + derived table + nested all in one

SUM() function + derived table + nested all in one

Could someone please give me a hand in explaining in this example where the SUM function would go? The count I a referring to is NOT the function but rather the name of a column on my table. I am trying to get a literal count on the number of returned columns

SELECT count, post_id
     , DeliveryDate
     , DeliveryType
  FROM ( SELECT count, post_id
              , MAX(CASE WHEN meta_key = 'value_1' THEN meta_value ELSE NULL END) as DeliveryDate
              , MAX(CASE WHEN meta_key = 'value_2' THEN meta_value ELSE NULL END) as DeliveryType
           FROM wp_postmeta
         GROUP 
             BY post_id 
       ) AS derived_table
       
 WHERE DeliveryDate >= CURRENT_DATE
   AND DeliveryType = 'delivery'

Solution

My feeling is that you want something along these lines:

SELECT
    post_id,
    COUNT(*) AS cnt,
    MAX(CASE WHEN meta_key = 'value_1' THEN meta_value END) AS DeliveryDate,
    MAX(CASE WHEN meta_key = 'value_2' THEN meta_value END) AS DeliveryType
FROM wp_postmeta
GROUP BY
    post_id
HAVING
    DeliveryDate >= CURRENT_DATE AND
    DeliveryType = 'delivery';

Instead of subquerying and then checking the delivery date and type in an outer WHERE clause, we can instead just do a single aggregation and assert your requirements in the HAVING clause, usign the aliases defined earlier.