CODEWITHSUNDEEP
phpmysqlcountgroup-by
George Ortiz
George Ortiz

Reputation: 309

Total Rows From Each GROUP BY

I'm trying to run a MySQL query and use PHP to do the following:

I need to group data in a column (GROUP BY) then count (COUNT) the number of rows in each group. After that, I need to divide the number of rows in a given group by the number of groups to get that groups percentage of popularity.

So if I had a table with the following data:

Version_Number
1.1
1.2
1.1
1.2
1.2
1.1

I need the final output to be:

1.1 50%
1.2 50%

Upvotes: 0

Views: 160

Answers (3)

George Ortiz
George Ortiz

Reputation: 309

This is what I ended up doing after trying the above:

$result = mysql_query("SELECT COUNT(*) AS nsites FROM table WHERE auth = '$auth'") or
                    die(mysql_error());
                    $return_sites = mysql_fetch_array($result);

                if (!$return_sites['nsites']) {

                echo "...";

                } else {

                    $esult = mysql_query("SELECT * FROM table WHERE auth = '$auth' GROUP BY theme_version") or die(mysql_error());
                    while($row = mysql_fetch_array($esult)){                        

                        $get_current_version = $row['theme_version'];

                        $vresult = mysql_query("SELECT COUNT(*) AS nversion FROM table WHERE auth = '$auth' AND theme_version = '$get_current_version'") or die(mysql_error());
                        $version = mysql_fetch_array($vresult);

                        $percent = round($version['nversion'] / $return_sites['nsites'] * 100);

                            echo "...";

                    }

                }

Upvotes: 1

Ian T. Small
Ian T. Small

Reputation: 298

You may need to adjust this for mysql (I normally work in oracle) but:

SELECT 
    ( count(*) / totalCount * 100) AS percentOfVersionedThings
    , Version_Number
FROM tableOfVersionedThings
    INNER JOIN ( SELECT count(*) as totalCount FROM tableOfVersionedThings ) ON 1=1
GROUP BY Version_Number

If this was Oracle, I'd suggest using analytics, but I'm not sure if there is an equivalent in MySQL. That said, in your case a simple sub-query should solve the problem, and it should be workable on any SQL database.

Upvotes: 1

lsl
lsl

Reputation: 4419

Something like this:

select v."Version_Number", count(v."Version_Number"),count(v."Version_Number")::float / (select count(v2."Version_Number")::float from versions v2 )::float * 100::float
from versions as v
group by v."Version_Number"

Upvotes: 0

Related Questions