Query to plot a histogram of song ratings I wrote code to plot a rating distribution, which looks like this:



The most important function is the stats function:

function stats($songID){
    $st=$this-&gt;conn-&gt;prepare("SELECT
        (SELECT COUNT(*) FROM list WHERE SongID=:songID),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='10'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='9.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='9'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='8.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='8'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='7.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='7'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='6.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='6'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='5.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='4.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='4'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='3.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='3'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='2.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='2'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='1.5'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='1'),
        (SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='0.5')
        FROM list WHERE SongID=:songID");
    $st-&gt;bindparam(":songID",$songID);
    $st-&gt;execute();
    return $st-&gt;fetchall();
}


function bars($rate,$percentage,$ratings){
    return &lt;&lt;&lt;HTML
    &lt;div class="progress border border-dark w-100 bg-transparent border-0 mt-1"&gt;
            &lt;span style="width:20px;" class="font-weight-bold text-light"&gt;
                {$rate}
            &lt;/span&gt;
            &lt;div class="progress-bar border border-dark bg-light" role="progressbar" style="max-width:25%;width:{$percentage}%;" aria-valuenow="{$ratings}" aria-valuemin="0" aria-valuemax="100"&gt;&lt;/div&gt;
            &lt;span class="font-weight-bold text-light ml-1 mr-1"&gt;
                {$percentage}%({$ratings} votes)
            &lt;/span&gt;
        &lt;/div&gt;
HTML;
}


And then in another file I use these methods:

$stats=$song-&gt;stats($_GET['songid']);
    foreach($stats as $key){
        $total=$key[0];
        $rate=10;
        for($i=1;$i&lt;21;$i++){
            echo $song-&gt;bars($rate,round(($key[$i]/$total)*100,2),$key[$i]);
            $rate-=0.5;
            }
            break;
        }


This code works perfectly, but somehow I'm not satisfied with the stats function, the way I solved it. It's just too long, and doesn't look quite good to me.

So the question is: is there a way to make that function a bit shorter, so I wouldn't repeat the same thing 20 times but with different values for the Rating column?

I was thinking that this might actually be the only way to do it, but I thought I'd give it a try here, since there are way smarter programmers here than I am.
It would be better to use GROUP by in your SQL. By this way you will get rid of long SQL query.

SELECT Rating, COUNT(id) FROM list WHERE songID=:songID GROUP BY Rating;