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`;
This question is related to
mysql
SELECT x.name, x.summary, (x.summary / COUNT(*)) as percents_of_total
FROM tbl t
INNER JOIN
(SELECT name, SUM(value) as summary
FROM tbl
WHERE year BETWEEN 2000 AND 2001
GROUP BY name) x ON x.name = t.name
GROUP BY x.name, x.summary
If you want to SELECT
based on the value of another SELECT
, then you probably want a "subselect":
http://beginner-sql-tutorial.com/sql-subquery.htm
For example, (from the link above):
You want the first and last names from table "student_details" ...
But you only want this information for those students in "science" class:
SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');
Frankly, I'm not sure this is what you're looking for or not ... but I hope it helps ... at least a little...
IMHO...
Source: Stackoverflow.com