Using mysql COUNT to count multiple columns

Question

I have information in a table like so:

id    name    recommender
 1    daniel  steve
 2    daniel  tony
 3    steve   daniel

and this code:

<h2>Follow/Join Recommendation League Table</h2>
<br/>
<?php

$query = "SELECT name_of_follower, COUNT(name) FROM recommendation_competition_entrants GROUP BY name_of_follower ORDER BY COUNT(name) DESC"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
        echo $row['name_of_follower'] . " has ". $row['COUNT(name)'] . " entries.";
        echo "<br />";
}

?>

This counts all entries in the 'name' column and displays a mini league table which looks like this:

daniel - 2 entries
steve - 1 entry

What I need to do now is count names from both the name & recommender columns so the table would look like this:

daniel - 3 entries
steve - 2 entries
tony - 1 entry

Is there a simple way to do this?

Thanks for any help

Mark Baker · Accepted Answer · 2011-04-21 11:22:00Z

1

select name_of_follower, 
       count(name_of_follower) 
  from ( select name as name_of_follower 
          from abc 
         union all 
        select follower as name_of_follower  
          from abc
       ) t
 group by t.name_of_follower 
 order by count(name_of_follower) desc

edited Apr 21, 2011 at 11:22

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

answered Apr 21, 2011 at 11:17

Ujjwal Manandhar

2,25417 silver badges20 bronze badges

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

1 Comment

Daniel H Over a year ago

thanks :) something else has come up but i'll try this out as soon as i get a chance

Dheeraj Kumar · Accepted Answer · 2011-04-21 11:10:58Z

0

SELECT SUM(COUNT(name) + COUNT(recommender)) FROM...

answered Apr 21, 2011 at 11:10

Dheeraj Kumar

1,00814 silver badges22 bronze badges

Collectives™ on Stack Overflow

Using mysql COUNT to count multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related