Help with count and Join

**Sinz** · Mar 5, 2011 03:03

I have this SQL query here:

PHP Code:


SELECT wp_posts.*, wp_postmeta.* FROM wp_posts, wp_postmeta WHERE wp_posts.ID=wp_postmeta.post_id  AND meta_key='image_path'  group by ID desc

however I would like to get the count total from this how can I do it?

**r937** · Mar 5, 2011 05:40

what exactly is it that you want to count?

what table does the ID in your GROUP BY clause belong to?

**Sinz** · Mar 5, 2011 12:20

Originally Posted by r937

what exactly is it that you want to count?

what table does the ID in your GROUP BY clause belong to?

I want to count the number of times the query finds a match from both tables, the ID I'm talking about is from wp_posts.ID

**r937** · Mar 5, 2011 15:48

do you want the count for each ID, or the total count overall?

**Sinz** · Mar 5, 2011 16:00

Originally Posted by r937

do you want the count for each ID, or the total count overall?

the total count for each ID

**r937** · Mar 5, 2011 19:24

Code:

SELECT post_id
     , COUNT(*)
  FROM wp_postmeta 
 WHERE meta_key = 'image_path'  
GROUP 
    BY post_id DESC

**Sinz** · Mar 6, 2011 00:01

Originally Posted by r937

Code:

SELECT post_id
     , COUNT(*)
  FROM wp_postmeta 
 WHERE meta_key = 'image_path'  
GROUP 
    BY post_id DESC

actually it didn't give the proper result I was looking for can you please show me the query for the total count overall?

thanks

**r937** · Mar 6, 2011 02:16

Originally Posted by Sinz

...can you please show me the query for the total count overall?

Code:

SELECT COUNT(*)
  FROM wp_postmeta 
 WHERE meta_key = 'image_path'

User Tag List