Question 268391 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql SUM with UNION ALL and JOIN

I have 2 tables and I like to get sum of values and also to group by the result.

I have this query:

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

and I get this answer:

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

The goal is to sum Christening with Christening etc, so the result will be :

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

I try this but give error:

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

Unknown column ‘projects.pr_type’ in ‘group statement’

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    5 min later , i decide to ask chatgpt, i was just curius! And i get this answer

    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;

  2. 0

    I have added the missing pr_type column in the subqueries and in the outer query, and I have used the CASE statement to sum the required values. This query will sum the total amounts based on the given conditions and group the results by 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search