Mysql Count per column

Question

I have the following query:

SELECT a.feeder_id, b.feeder_pr
FROM authors_article_feeders a
LEFT JOIN feeders b ON b.id = a.feeder_id
WHERE website_id =1
LIMIT 0 , 30

which results in:

feeder_id   feeder_pr
18          2
18          2
18          2
18          2
32          6

What I need is to modify the above query so that it will manipulate this data so that the result would end up with a count of each feeder_pr, so in this case the result would be:

feeder_pr   count
2           4
6           1

Any assistance is appreciated. If you have time please describe your solution so that I can learn from it while I'm at it.

Everything I've tried has ended in inaccurate results, usually with just one row instead of the expected 2.

What you need is select b.feeder_pr,count(b.feeder_pr) group by b.feeder_pr with your existing joins, you may not by giving the group by thats why you end up with one row — prajeesh kumar
– prajeesh kumar, Commented Mar 14, 2012 at 3:48

Justin Pihony · Accepted Answer · 2012-03-14 03:49:50Z

4

You just need to add a GROUP BY And, you would not even need the joins

SELECT b.feeder_pr, COUNT(b.feeder_pr)
FROM feeders b 
GROUP BY b.feeder_pr

answered Mar 14, 2012 at 3:49

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

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

4 Comments

Justin Pihony Over a year ago

No problem, please don't forget that appreciation is best shown with a checkmark :)

Scott Rowley Over a year ago

:) I plan to in 4 minutes when it will let me ;)

Justin Pihony Over a year ago

Oh haha, I didnt realize there was a time limit on that. Thanks!

Scott Rowley Over a year ago

Yea, I'm not sure what it is, maybe 10-15 minutes or so after the initial question is asked.

Bohemian · Accepted Answer · 2012-03-14 03:50:45Z

0

SELECT b.feeder_pr, count(a.feeder_id) as count
FROM authors_article_feeders a
LEFT JOIN feeders b ON b.id = a.feeder_id
WHERE website_id =1
GROUP BY 1

answered Mar 14, 2012 at 3:50

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

Mysql Count per column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related