mysqlcountsumhaving-clausederived-table

count() function + derived table and Group - making it all work


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';

Here is an image of what the above produces image of what this above clause does. It seems to be counting the meta_key results of each with a post ID that matches. It is not groping them when set up this way


Solution

  • 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