Simple Percentage Calculation in MySQL

MySQL Calculate Percentage

try this

   SELECT group_name, employees, surveys, COUNT( surveys ) AS test1, 
        concat(round(( surveys/employees * 100 ),2),'%') AS percentage
    FROM a_test
    GROUP BY employees

DEMO HERE

Simple Percentage Calculation in MySQL

As others have commented, you can't do SUM(COUNT(*)) in SQL.

I assume you want the percentage to be the count per group within the date range over the total for all groups within the same date range.

I would calculate the total as a subquery in the FROM clause, so it executes the subquery once and reuses its result.

SELECT DR.state, COUNT(*) AS count, COUNT(*) / total.count AS pct
FROM flow.run AS DR
JOIN flow.job AS J ON DR.id = J.id
CROSS JOIN (
  SELECT COUNT(*) AS count 
  FROM flow.run AS DR
  JOIN flow.job AS J ON DR.id = J.id
  WHERE DR.start_date > '2018-12-08'
) AS total
WHERE DR.start_date > '2018-12-08'
GROUP BY DR.state

Remember to use the same date range condition in the subquery. There's no way the query can guess that you meant the rows counted in the subquery from the same date range just because a condition exists on the outer query.

MySQL - Calculate percentage and count number of related records

Your third part isn't clear. But the second part can be solved by:

SELECT
    M.name AS title_movie,
    M.year AS premiere,
    G.name AS gender,
    COUNT(MHR.vote_id) AS total_voting,
    -- 2)
    COUNT(MHR.vote_id = 1 OR NULL) AS count_very_good,
    COUNT(MHR.vote_id = 2 OR NULL) AS count_good,
    COUNT(MHR.vote_id = 3 OR NULL) AS count_regular
FROM movies M
LEFT JOIN gender G ON M.gender_id = G.id
LEFT JOIN movies_has_rating MHR ON M.id = MHR.movie_id
GROUP BY M.id
ORDER BY M.id DESC

http://sqlfiddle.com/#!9/5ba403/5

I'm sure you know how to calculate the percentages from that result.

Update: So OP asks to determine the vote type with the highest count and calculate its percantage within SQL. You can (but you should not) do it with the folowing query:

SELECT t.*, 
    CASE
        WHEN highest_votes_id = 1
            THEN 100 * count_very_good / total_voting
        WHEN highest_votes_id = 2
            THEN 100 * count_good / total_voting
        WHEN highest_votes_id = 3
            THEN 100 * count_regular / total_voting
    END AS highest_votes_percentage
FROM (  
    SELECT t.*,
        CASE
            WHEN count_regular > count_good AND count_regular > count_very_good
                THEN 3
            WHEN count_good > count_very_good
                THEN 2
            ELSE 1
        END AS highest_votes_id
    FROM (
        SELECT
            M.id,
            M.name AS title_movie,
            M.year AS premiere,
            G.name AS gender,
            COUNT(MHR.vote_id) AS total_voting,
            COUNT(MHR.vote_id = 1 OR NULL) AS count_very_good,
            COUNT(MHR.vote_id = 2 OR NULL) AS count_good,
            COUNT(MHR.vote_id = 3 OR NULL) AS count_regular
        FROM movies M
        LEFT JOIN gender G ON M.gender_id = G.id
        LEFT JOIN movies_has_rating MHR ON M.id = MHR.movie_id
        GROUP BY M.id
    ) t
) t
ORDER BY t.id DESC

http://sqlfiddle.com/#!9/5ba403/7

How ever you solve that problem in SQL, you will end up with unreasonable ugly code. You better do it in PHP.

if ($row['count_regular'] > $row['count_good'] &&
    $row['count_regular'] > $row['count_very_good']
) {
    $highestVoteType  = 'Regular';
    $highestVoteCount = $row['count_regular'];
} elseif ($row['count_good'] > $row['count_very_good']) {
    $highestVoteType  = 'Good';
    $highestVoteCount = $row['count_good'];
} else {
    $highestVoteType  = 'Very Good';
    $highestVoteCount = $row['count_very_good'];
}
if ($row['total_voting'] != 0) {
    $highestVotePercantage = 100 * $highestVoteCount / $row['total_voting'];
} else {
    $highestVotePercantage = null;
}

MYSQL how to calculate a percentage value with a join query

This query will give you the results you want:

SELECT u.id, COALESCE(ROUND(p.amount / (u.total + u.vat + u.government_tax) * 100), 0) AS payment_percentage
FROM user_packages u
LEFT JOIN (SELECT package_id, SUM(amount) AS amount
           FROM payments
           GROUP BY package_id) p ON p.package_id = u.id

Output:

id  payment_percentage
1   94
2   0
3   100

Demo on dbfiddle

Calculate percentage two values and sum arithmetic operations mysql

• Use aggregation functions like SUM().
• Do Group By on TypePayment_Id.
• Logically, you will need to calculate commission value per row; because commission percent may vary. Then, SUM them up to compute total commission paid for a TypePayment_Id.

Try (for MySQL):

SELECT  
  `TypePayment_Id`, 
  SUM(`Value`) AS `Total`, 
  SUM(`Value` * `Percent` / 100) As `Commission`
FROM `your_table` 
GROUP BY 
  `TypePayment_Id`

---

Related Topics