How to get sum of same value and different value in same row?

Question

table 1
invno percentage cost
1 18% 18.00
1 18% 18.00
2 18% 18.00
2 28% 28.00

table 2
id percentage
1 18%
2 28%

The table 2 percentage column values should become the column headings of output.

In table 1, invno 1 has 2 entries, but the same percentage value of 18%; invno 2 has 2 entries with different percentage values.

output

invno    percentage 18%    percentage 28%
  1           36.00             0.00
  2           18.00            28.00

So far I have written:

SELECT
    `invno`,
    SUM(CASE WHEN `percentage` = '18' THEN `percentage` ELSE NULL END) AS `percentage_18`,
    SUM(CASE WHEN `percentage` = '28' THEN `percentage` ELSE NULL END) AS `percentage_28`
FROM `table1`
GROUP BY `invno`
HAVING 18 IS NOT NULL AND 28 IS NOT NULL
ORDER BY `invno`

This is fine, but I want to get the percentages dynamically.

*please give me mysql query for this output* how about trying something yourself first. — Nigel Ren, Apr 28 '19 at 07:21
i don't know how to ask question in here . this my first time .so please help me — Rohith, Apr 28 '19 at 07:31
If I understand your task, you will need to write some kind of a JOIN (depending on your needs), then use a pivot technique to generate the sums. Here's a lead...https://stackoverflow.com/a/51321042/2943403 — mickmackusa, Apr 28 '19 at 07:33
The question is fine as far as it goes, but as pointed out - you are expected to make some attempt at solving the problem. Have you written any code so far? — Nigel Ren, Apr 28 '19 at 07:33
i don't know how to set table2 result as a output column name . — Rohith, Apr 28 '19 at 07:45
@Rohith you may need to query table 2 first, then construct a dynamic query on table 1 using the results from table 1. — mickmackusa, Apr 28 '19 at 12:06
@mickmackusa for me the question was not even closed expecially for a new contributor — ScaisEdge, Apr 28 '19 at 12:07
@Rohith I whacked together a quick working solurion on my localhost. I'll post a solution once the page is reopened. Just a refinement in advance, I recommend that you remove the trailing `%` from your table values and set the column type as int or decimal depending on your expected percentage values. — mickmackusa, Apr 28 '19 at 22:25
A closed question that has been edited goes into a "queue" to be reviewed. If 5 volunteers decide that the question has been edited to become answerable, then their 5 votes re-open the question. (beyond the Review Queue, if any passer-by decide that it should not be closed, they can vote to re-open as well. — mickmackusa, Apr 29 '19 at 04:40

score 0 · Accepted Answer · answered Apr 29 '19 at 04:38

Over a bowl of cereal this morning, I quickly scratched out a two-step approach to generate your desired output in a dynamic fashion.

if (!$conn = new mysqli("localhost", "root","","dbname")) {
    echo "Database Connection Error"; // $conn->connect_error
} else {
    if (!$result = $conn->query("SELECT DISTINCT percentage FROM percents ORDER BY percentage")) {
        echo "Syntax Error"; // $conn->error
    } else {
        $select_columns = ['invno'];
        foreach ($result as $row) {
            $percent = (int)$row['percentage'];
            $select_columns[] = "SUM(CASE WHEN percentage = {$percent} THEN `percentage` ELSE 0 END) AS percentage_{$percent}";
        }
        if (!$result = $conn->query("SELECT " . implode(', ', $select_columns) . " FROM invoices GROUP BY invno ORDER BY invno")) {
            echo "Syntax Error"; // $conn->error
        } else {
            echo "<table border=1>";
                foreach ($result as $index => $row) {
                    if (!$index) {
                        echo '<tr><th>' , implode('</th><th>', array_keys($row)) , '</th></tr>';
                    }
                    echo '<tr><td>' , implode('</td><td>', $row) , '</td></tr>';
                }
            echo "</table>";
        }
    }
}

Effectively, I queried for unique percentage values from table2, then used those values to dynamically construct the SUM(CASE...)) expressions on table1. If this doesn't exactly work as you require on your actual project data, please supply a better/larger representation of your db data which isolates the issue so that I can update my answer.

How to get sum of same value and different value in same row?

1 Answers1