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  quot   of Total quot  FROM  table1  WHERE  Year  BETWEEN 2000 AND 2001 GROUP BY  Name

User · Answer

If you want to SELECT based on the value of another SELECT  then you probably want a  quot subselect quot   http   beginner-sql-tutorial com sql-subquery htm For example   from the link above    You want the first and last names from table  quot student details quot       But you only want this information for those students in  quot science quot  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

User · Answer

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

User · Answer

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

[mysql] How to SELECT based on value of another SELECT

Examples related to mysql