MySQL - How to SELECT based on value of another SELECT
I have a table that looks something like this:
Name Year Value A 2000 5 A 2001 3 A 2002 7 A 2003 1 B 2000 6 B 2001 1 B 2002 8 B 2003 2
The user can query based on a range of years, and it will return the sum of Value grouped by Name, as such (assume queried years are 2000 - 2001):
Name SUM(Value) A 8 B 7
Now, I wanted to insert a calculated field that outputs the ratio of the sum of the values of each name for ALL years to the sum of all values. Basically the percentage of all values attributed to A and B, respectively, like:
Name SUM(Value) % Of Total A 8 0.484 (16 / 33) B 7 0.516 (17 / 33)
Note that even though the user queried only 2000-2001, I want the calculation to use the sum across all years. I've been searching and experimenting for hours and I cannot figure out how. I only know how to sum across the queried years like so:
SELECT `Name`, SUM(`Value`)/(SELECT SUM(`Value`) FROM `table1`) AS "% of Total" FROM `table1` WHERE `Year` BETWEEN 2000 AND 2001 GROUP BY `Name`;
Please help! I'm very much a novice, and don't understand SQL in much depth, so please clarify any advanced code. Thank you for your kindness.
You can calculate the total (and from that the desired percentage) by using a subquery in the FROM clause:
SELECT Name, SUM(Value) AS "SUM(VALUE)", SUM(Value) / totals.total AS "% of Total" FROM table1, ( SELECT Name, SUM(Value) AS total FROM table1 GROUP BY Name ) AS totals WHERE table1.Name = totals.Name AND Year BETWEEN 2000 AND 2001 GROUP BY Name;
Note that the subquery does not have the WHERE clause filtering the years.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]