mariadb MySQL SUM与UNION ALL和JOIN

ua4mk5z4 于 2023-10-20 发布在 Mysql

关注(0)|答案(2)|浏览(163)

我有2个表，我喜欢得到的值和也组的结果。
我有这个疑问：

SELECT SUM(tr_amount) as total, projects.pr_type
    from transactions 
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_amount < '0'
    GROUP BY projects.pr_type

UNION ALL 

SELECT SUM(tr_pr_price) as total, projects.pr_type
    from transactions 
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_type = '1' 
    GROUP BY projects.pr_type

我得到了这样的答案：

+-------+-----------------------+
| total | pr_type               |
+-------+-----------------------+
| -3720 | Christening           |
| -1640 | Wedding               |
|  -820 | Wedding - Christening |
| 10410 | Christening           |
|   350 | Photoshooting         |
|  6650 | Wedding               |
|  2500 | Wedding - Christening |
+-------+-----------------------+

我们的目标是总结洗礼与洗礼等，所以结果将是：

+-------+-----------------------+
| total | pr_type               |
+-------+-----------------------+
|  6690 | Christening           |
|   350 | Photoshooting         |
|  5010 | Wedding               |
|  1680 | Wedding - Christening |
+-------+-----------------------+

我尝试这一点，但给予错误：

SELECT SUM(total) FROM(
SELECT SUM(tr_amount) as total, projects.pr_type
    from transactions 
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_amount < '0'
    GROUP BY projects.pr_type

UNION ALL 

SELECT SUM(tr_pr_price) as total, projects.pr_type
    from transactions 
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_type = '1' 
    GROUP BY projects.pr_type) as test
    GROUP BY projects.pr_type

“group statement”中的未知列“projects.pr_type”

mariadb

来源：https://stackoverflow.com/questions/77323598/mysql-sum-with-union-all-and-join

2条答案

按热度按时间

vngu2lb81#

我在子查询和外部查询中添加了缺少的pr_type列，并使用CASE语句对所需的值求和。这个查询将根据给定的条件对总金额进行求和，并按pr_type对结果进行分组。

SELECT 
pr_type, 
SUM(total) as total_sum FROM (
SELECT 
    projects.pr_type, 
    SUM(CASE WHEN tr_amount < 0 THEN tr_amount ELSE 0 END) AS total
FROM transactions 
INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
GROUP BY projects.pr_type

UNION ALL

SELECT 
    projects.pr_type, 
    SUM(CASE WHEN tr_type = '1' THEN tr_pr_price ELSE 0 END) AS total
FROM transactions 
INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
GROUP BY projects.pr_type ) AS test GROUP BY pr_type;

赞(0）回复(0）举报 2023-10-20

g9icjywg2#

5分钟后，我决定问chatgpt，我只是好奇！我得到了这样的答案

SELECT pr_type, SUM(total) as combined_total
FROM (
    SELECT SUM(tr_amount) as total, projects.pr_type
    FROM transactions
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_amount < '0'
    GROUP BY projects.pr_type

    UNION ALL

    SELECT SUM(tr_pr_price) as total, projects.pr_type
    FROM transactions
    INNER JOIN projects ON projects.pr_name = transactions.tr_pr_name
    WHERE tr_type = '1'
    GROUP BY projects.pr_type
) AS subquery
GROUP BY pr_type;

赞(0）回复(0）举报 2023-10-20

我来回答

mariadb MySQL SUM与UNION ALL和JOIN

2条答案

相关问题

热门标签

最新问答