MySQL - How to SELECT based on value of another SELECT


Problem :

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.



Solution :

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.


Mysql Tutorials

Mysql Howto..

How to update Mysql using array in php in a dynamic way

How to check best-attending employees of the year [closed]

Good tutorial on how to update your Mysql database with a PHP form? [closed]

How to access MySQL database when given localhost access?

How to update several MySQL DBs together?

how to fetch record from Mysql in PHP manually? [closed]

How do I use DATE_FORMAT inside my MySQL statement in Python?

How to insert a floating-point number in the field of type DECIMAL for MySQL?

MySQL Database has non escaped single quotes in entires … How to show them?

How to create a trigger with multiple actions in MySQL 5.0.45?

How do you version and sync your MySQL data model?

How to insert image to mysql database in netbeans java

How to retrieve data from mysql database from php (from for loop)

how to add update entity in mysql?

How to Exclude Data for Specific Tables

How to get lowest value for 2 duplicate entries in PHP and MySQL

Silex and Doctrine mysql: how to return results from select all

how to edit php.ini in CLI

How can I return mysql columns from separate tables with one stored procedure?

How to retrieve products by tags (SQL question)

How do I convert negative time value into 24 hour time in MYSQL?

How to use new format

(mysql, php) How to get auto_increment field value before inserting data?

How to print month name from MySQL timestamp count in PHP?

MySQL SELECT with JOIN - how to code correct

How to load updated data from database(mysql) without refresh the page? [closed]

MySQL - Many to One from Room to Apartment: how to get the lowest priced room for an apartment without having to iterate through all the rooms?

How to decrease MySQL connection time

How to use mySql DB from C# on shared hosting?

How to get count of records month-wise from mysql table?