How to SELECT based on value of another SELECT

Question

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`;

score 115 · Accepted Answer · answered Apr 02 '12 at 03:36

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.

Easiest to understand and worked like a charm. Thank you so much! — John, Apr 02 '12 at 14:28

score 24 · Answer 2 · edited Feb 19 '23 at 16:47

24

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');

edited Feb 19 '23 at 16:47

halfer

19,824
17
99
186

answered Apr 02 '12 at 03:20

paulsm4

114,292
17
138
190

1

The sample is not helpful at all, as long as subquery makes no sense there – zerkms Apr 02 '12 at 03:25
I have that much down, but I can't figure out the syntax. I'm trying to include a value from an inner table that relies on the value from the outer table. – John Apr 02 '12 at 03:29
Book recommendation: http://www.amazon.com/SQL-Queries-Mere-Mortals-Manipulation/dp/0321444434 Personally, I've found it very helpful. I honestly think you might like it, too. IMHO... – paulsm4 Apr 02 '12 at 04:33
2

`SELECT id, first_name FROM student_details WHERE subject = 'Science'` isnt it exactly same? – Roni Tovi Jun 29 '19 at 14:08
Quick note: it is very normal for editors to remove "hope this helps" and other conversational asides. There is a broad consensus to use technical writing here. – halfer Feb 19 '23 at 16:48

score 4 · Answer 3 · answered Apr 02 '12 at 03:23

4

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

answered Apr 02 '12 at 03:23

zerkms

249,484
69
436
539

How to SELECT based on value of another SELECT

3 Answers3

Linked