Could someone please give me a hand in explaining in this example where the SUM function would go? I am trying to get a literal count on the number of returned columns, NOT the number of meta_key's for each post id. I want a number like there are 5 results AFTER it groups.
SELECT nmbr, post_id
, DeliveryDate
, DeliveryType
FROM ( SELECT nmbr, 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'
I have tried but it doesnt count or sum anything and there is something about the HAVING clause that causes no results to return. If I remove that it shows all results on the table but its not counting them.
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';
SELECT COUNT(*)
FROM (
SELECT nmbr, post_id
, DeliveryDate
, DeliveryType
FROM ( SELECT nmbr, 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'
) AS q