mysql - MySQL Populate Table from another Table through multiple aggregate queries at a same time

Question

OverView: In Mysql Database, I have already one table populated and I want to populate another table with two common fields and three aggregated fields from the 'already populated' table. I can do it by separate queries but can't find a way to do it with single query consisting of sub-queries.

Problem: SQLFIDDLE As I have shared the minimal schema here in this fiddle, my ultimate target is to populate "company_value" table which have the fields like 'total','export_val' and 'import_val'. The source table is 'company_products' which have 'amount' column.

I have tried to do 'join' three queries on condition (companyCode and trPeriod same) where all of the three queries are from the same table. And, unfortunately I got the famous mysql error [You have an error in your SQL syntax....].

In the fiddle, I tried a way of making the aggregated queries and joining to create at least the expected output for the target table. I have also tried similar ways with Mysqls PYTHON ORM Peewee as well. Still can't get through.

Please pardon my inexperienced query writing skill for join queries as a newbie in SQL. Any help from you guys will be great for me. TIA

标签： mysql

Answer 1

更好的方法可能是条件聚合

select companycode,trperiod,    
         sum(Case when exim = 'ex' then amount else 0 end) as exports,
         sum(Case when exim = 'im' then amount else 0 end) as imports,
         sum(amount) as Total
from company_products
group by companycode,trperiod;

结果

+-------------+----------+---------+---------+---------+
| companycode | trperiod | exports | imports | Total   |
+-------------+----------+---------+---------+---------+
| abcd111     | 201801   |  559.00 |    0.00 |  559.00 |
| abcd111     | 201802   |  550.00 |    0.00 |  550.00 |
| abcd111     | 201803   |  507.00 |    0.00 |  507.00 |
| abcd112     | 201801   |    0.00 |   56.00 |   56.00 |
| abcd113     | 201801   |    0.00 |  110.00 |  110.00 |
| abcd114     | 201801   |  234.00 |  234.00 |  468.00 |
| abcd115     | 201801   |  765.00 |  765.00 | 1530.00 |
| abcd116     | 201801   |  321.00 |  321.00 |  642.00 |
+-------------+----------+---------+---------+---------+
8 rows in set (0.00 sec)