CODEWITHSUNDEEP
phpsql
Robert Rouge
Robert Rouge

Reputation: 289

group rows, count grouped rows and order by grouped rows

I want to make an analytics system for a website and I am trying to row accesed urls from a db, group by url, count grouped rows and order DESC by number of grouped rows.

$sql = "SELECT COUNT(*) FROM (SELECT DISTINCT url FROM analytic) ORDER by (SELECT DISTINCT url FROM analytic)";
$countQry  = mysqli_query($link, $sql);

while($arr = mysqli_fetch_array($countQry)) {
?>
    <?=$arr['url']?>

<?
    }
?>

thanks

Upvotes: 1

Views: 112

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

Try using a GROUP BY:

SELECT url, COUNT(url) AS theCount
FROM analytic
GROUP BY url
ORDER BY theCount DESC

Here is PHP code for you to use:

$sql = "SELECT url, COUNT(url) AS theCount FROM analytic GROUP BY url ORDER BY by theCount DESC";
$countQry  = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($countQry, MYSQLI_ASSOC)) {
    echo $row['url'], ", ", $row['theCount'];
}

Upvotes: 1

Related Questions