MySQL joined query count returning a row for each result

So, I'm doing a custom wordpress query to pull events (Events are stored in custom fields (meta data) hence the need for the JOINs) and I want to get a count of the rows returned. My problem is that I'm getting a bunch of rows returned each containing a count against each post ID returned.

My query looks a bit like this:

SELECT count(*)
FROM   wp_posts AS wposts 
   LEFT JOIN wp_postmeta AS metafeatured 
          ON wposts.id = metafeatured.post_id 
   LEFT JOIN wp_postmeta AS metastart 
          ON wposts.id = metastart.post_id 
   LEFT JOIN wp_postmeta AS metaend 
          ON wposts.id = metaend.post_id 
   LEFT JOIN wp_term_relationships 
          ON ( wposts.id = wp_term_relationships.object_id ) 
   LEFT JOIN wp_term_taxonomy 
          ON ( wp_term_relationships.term_taxonomy_id = 
               wp_term_taxonomy.term_taxonomy_id ) 
WHERE  wposts.post_type = 'll_events' 
   AND wposts.post_status = 'publish' 
   AND ( ( ( metastart.meta_key = 'll_events_startdate' 
             AND Cast(metastart.meta_value AS UNSIGNED) < 1342997940 ) 
           AND ( metaend.meta_key = 'll_events_enddate' 
                 AND Cast(metaend.meta_value AS UNSIGNED) > 1342525664 ) ) 
          OR ( ( metastart.meta_key = 'll_events_startdate' 
                 AND Cast(metastart.meta_value AS UNSIGNED) <= 1342997940 ) 
               AND ( metaend.meta_key = 'll_events_enddate' 
                     AND Cast(metaend.meta_value AS UNSIGNED) >= 1342525664 
                   ) ) 
          OR ( ( metastart.meta_key = 'll_events_startdate' 
                 AND Cast(metastart.meta_value AS UNSIGNED) BETWEEN 
                     1342525664 AND 1342997940 
                   ) 
               AND ( metastart.meta_key = 'll_events_enddate' 
                     AND Cast(metaend.meta_value AS UNSIGNED) BETWEEN 
                         1342525664 AND 1342997940 
                   ) ) 
          OR ( ( metastart.meta_key = 'll_events_startdate' 
                 AND Cast(metastart.meta_value AS UNSIGNED) BETWEEN 
                     1342525664 AND 1342997940 
                   ) 
               AND ( metaend.meta_key = 'll_events_enddate' 
                     AND Cast(metaend.meta_value AS UNSIGNED) <= 1 ) ) ) 
GROUP BY wposts.ID, metastart.post_id, metaend.post_id, wp_term_relationships.object_id
ORDER BY metastart.meta_value ASC 

I've tried loads of combinations of group, count and such and can't get the expected result (a single row in the result-set containing a 'count'). Instead, my resultset looks something like:

count(*)
24
30
30
30
12
16
1

... and so on, where I need:

count(*)
139

Answers


Just remove the GROUP BY Clause


Need Your Help

Sync Two MediaWikis weekly?

php mysql mediawiki wikipedia

We have two mediawiki (like wikipedia) installations. One is public and one is internal.

Calling Form From VBA

vba ms-access access-vba ms-access-2010

I created a VBA function in Access 2010 which opens a progress bar in "Form!" and increases the size of "frame1" incrementally and updates a caption box. Running this function in "Step Into" debug ...