SELECT COUNT(*)

Can you please point me in the right direction. I have a SQL query and it works.

SELECT
p.post_title,
m1.meta_value
AS footballteam_value
FROM wp_posts
AS p
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = 'footballteam_value'
WHERE p.post_type = 'team'
AND post_status = 'publish'
GROUP BY m1.meta_value

The report shows me all my different football teams

I am now trying to count how many posts there are with this football team

I am guessing I need to use the “SQL Query for Count” so that I can add a column to the report with the sum of how many posts with this football team?

So far I have this but its not working, I just get zeros in the column

SELECT COUNT(*)
FROM $wpdb->posts p, $wpdb->postmeta p1
WHERE p.post_status = 'publish' AND p.ID = p1.post_id
AND p1.meta_key = 'footballteam_value'
AND p1.meta_value = '$item'

I have also added the field name of $item, with data type number…..

Any help would be much appreciated….

Many Thanks

https://www.ads-software.com/extend/plugins/exports-and-reports/