MySQL count rows with same values

Question

i have been searching but i didnt find what i was looking for. This is what i have:

SELECT user_email, post_type
FROM `wp_users`
INNER JOIN `wp_posts` ON wp_users.id = wp_posts.post_author
WHERE post_type LIKE '%topic%'
OR post_type LIKE '%reply%

it bring what i needed: I needed a query that shows me the email of an user and how many topics and replys he made (BBPRESS).

The problem is that it brings all of them but it doesnt tell me how many topics / replies the user made. This is what it brings: https://i.sstatic.net/sMymU.png Is there any way to add a 3rd column where it counts?

You can group by user_email and do a count on the post_type column. — Guranjan Singh
– Guranjan Singh, Commented Jun 15, 2015 at 17:35

Giorgi Nakeuri · Accepted Answer · 2015-06-15 17:40:51Z

1

Conditional aggregation:

SELECT user_email, 
       SUM(CASE WHEN post_type LIKE '%reply%' THEN 1 ELSE 0 END) AS Replies,
       SUM(CASE WHEN post_type LIKE '%topic%' THEN 1 ELSE 0 END) AS Topics
FROM `wp_users`
INNER JOIN `wp_posts` ON wp_users.id = wp_posts.post_author
WHERE post_type LIKE '%topic%' OR post_type LIKE '%reply%'
GROUP BY user_mail

answered Jun 15, 2015 at 17:40

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim3880 · Accepted Answer · 2015-06-15 17:40:09Z

0

Try this:

  SELECT user_email,
    SUM(CASE WHEN post_type LIKE '%topic%' THEN 1 ELSE 0 END) AS Topics, 
    SUM(CASE WHEN post_type LIKE '%reply%' THEN 1 ELSE 0 END ) AS Replies
  FROM `wp_users`
  INNER JOIN `wp_posts` ON wp_users.id = wp_posts.post_author
  GROUP BY user_email

answered Jun 15, 2015 at 17:40

Tim3880

2,5831 gold badge13 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL count rows with same values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related