mysql - 如何在mysql中找到组的总和并找到权重？

Question

我有一张公司表格，结构如下:

 +------------+--------------+-----------+--------+-----------------------------+
 | ticker     | total_shares | institute | public | sector                      |
 +------------+--------------+-----------+--------+-----------------------------+
 | 1JANATAMF  |    200000000 |     30.00 |  45.00 | Mutual Funds                |
 | 8THICB     |      5000000 |      0.00 |   0.00 | Mutual Funds                |
 | AAMRATECH  |     55369395 |     28.00 |  42.00 | IT Sector                   |
 | ABB1STMF   |    162615643 |     46.44 |  33.56 | Mutual Funds                |
 | ABBANK     |    532459152 |     54.86 |  30.01 | Bank                        |
 | ACI        |     28661768 |     37.73 |  28.23 | Pharmaceuticals & Chemicals |
 | ACIFORMULA |     45000000 |     22.77 |  11.21 | Pharmaceuticals & Chemicals |
 | ACIZCBOND  |       267170 |     60.00 |  40.00 | Corporate Bond              |
 | ACTIVEFINE |     69000000 |     20.80 |  64.42 | Pharmaceuticals & Chemicals |
 | AFCAGRO    |     50000000 |     48.11 |  21.60 | Pharmaceuticals & Chemicals |
 | AFTABAUTO  |     95732422 |     47.49 |  22.01 | Engineering                 |
 | AGNISYSL   |     51798982 |     16.33 |  70.92 | IT Sector                   |
 | AGRANINS   |     24157650 |     18.30 |  43.05 | Insurance                   |
 | AIBL1STIMF |    100000000 |     76.31 |  13.69 | Mutual Funds                |
 | AIMS1STMF  |     41450000 |      0.00 |   0.00 | Mutual Funds                |
 | AL-HAJTEX  |     12691843 |      4.19 |  71.76 | Textile                     |
 | ALARABANK  |    946958501 |     10.07 |  46.88 | Bank                        |
 +------------+--------------+-----------+--------+-----------------------------+

另一个价格表:

 +------------+--------+--------+------------+
 | ticker     | open   | high   | price_date |
 +------------+--------+--------+------------+
 | AAMRATECH  |  29.80 |  30.20 | 2014-05-04 |
 | ABBANK     |  27.50 |  27.50 | 2014-05-04 |
 | ACI        | 235.00 | 244.50 | 2014-05-04 |
 | ACIFORMULA |  86.00 |  87.60 | 2014-05-04 |
 | ACTIVEFINE |  69.00 |  70.00 | 2014-05-04 |
 | BANGAS     | 509.50 | 509.50 | 2014-05-04 |
 | BANKASIA   |  18.00 |  18.40 | 2014-05-04 |
 +------------+--------+--------+------------+

当然，出于某种目的，数据已被缩短。现在我想找到开盘的加权价格。其公式为

A = open*((total_share*(institue/100)+(total_share*(public/100))

按部门划分的 A 总和，例如所有股票代码的总和都属于共同基金。然后除以单个股票代码(例如 1JANATAMF/共同基金总和)即可找到权重。

我使用过这个查询:

select c.ticker, 
p.open*(c.total_shares*(c.institute/100)+c.total_shares*(c.public/100)) as cap
from company as c
left join price as p
on p.ticker =c.ticker
where p.price_date = '2014-05-04';

但找不到按扇区求和并求出权重的方法。

编辑:
输出将类似于
股票代码，A/(按扇区分组的 A 之和)其中，A是根据ticker制定的值

输出将类似于:

 +------------+--------------+--------------+
 | ticker     | sum(a)       | weight       |
 +------------+--------------+--------------+
 | 1JANATAMF  |   5700000000 |    150078540 |

Answer 1

在某些 View 中中断您的查询，看看它是否是您在 SQLFiddle 中需要的 http://sqlfiddle.com/#!2/7b4cb/4/2