CODEWITHSUNDEEP
phpmysql
Steeve
Steeve

Reputation: 443

mysql query to count and sum

i have a table where same data is appearing again and again the table structure is like below.id is autoincrement.pm is for userid.it will appear again and again like beliw table.i want to display the output as the extract of entire table in descending order. suppose the table consists of 100 rows and the userid 15 comes 10 times which is highest then it should come at the top then the other id which is coming next like that . i tried but not working here is my code as well

id    userid
1      33
2      34
3      37
4      33 
5      33
6      37

the output i want is 

     userid   nos
      33       3
      37       2
      34       1

please guide . i tried with this code

$res = sql_query("select count(userid) as total from tableA");

echo'<table>';
while ($row = sql_fetch_array($res)) {
echo ' <tr><td>'.$row['userid'].'</td></tr></table>';

Upvotes: 0

Views: 1822

Answers (3)

gulyan
gulyan

Reputation: 662

You need to use a GROUP BY statement

SELECT userid, COUNT(userid) AS nos FROM tableA
    GROUP BY userid ORDER BY nos DESC;

Upvotes: 3

Evan Mulawski
Evan Mulawski

Reputation: 55334

SELECT userid, COUNT(userid) FROM tableA AS nos GROUP BY userid ORDER BY nos DESC

Then you can access:

$row['userid'] for the user ID

$row['nos'] for the count

Upvotes: 2

hjpotter92
hjpotter92

Reputation: 80639

Your looping methodology is wrong.

$res = sql_query("select userid, count(userid) as total from tableA group by userid order by total desc");
echo'<table>';
while ($row = sql_fetch_array($res)) {
  echo ' <tr><td>'.$row['userid'].'</td><td>'.$row['total'].'</td></tr>';
}
echo '</table>';

Upvotes: 0

Related Questions