-1

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

Tim
  • 17
  • 6
  • 1
    What is *a literal count on the number of returned columns*? Sample data, current and desired results would help clarifying your question. – GMB Aug 20 '20 at 22:57
  • @GMB Have to return the meta data values with the CASE then group. Then I want to count. The above is actually all there is on this query. This isnt truncated. Just added more details – Tim Aug 20 '20 at 23:06
  • re "count on the number of returned columns" returned by what? please add sample data and show your desired results for that data – ysth Aug 20 '20 at 23:24
  • How do I add sample data? – Tim Aug 20 '20 at 23:28
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Aug 21 '20 at 06:34

1 Answers1

0
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
Tim
  • 17
  • 6