MySQL] Group By 이후 SUM

1. ChatGPT 검색 결과

• 질문한 내용: sum after group by in mysql
• chatgpt의 답변에서 아이디어를 얻음(subquery 사용)
• 아래는 chatgpt의 답변

To calculate the sum of a column after using the GROUP BY clause in MySQL, you can make use of the GROUP BY clause along with the SUM() function in a subquery. Here's an example:

SELECT group_column, SUM(column_to_sum) AS sum_result
FROM (
    SELECT group_column, column_to_sum
    FROM your_table
    GROUP BY group_column, column_to_sum
) AS subquery
GROUP BY group_column;

In the above query, replace your_table with the actual name of your table. group_column represents the column(s) by which you want to group the data, and column_to_sum is the column for which you want to calculate the sum.

 

For example, if you have a table named "orders" with columns "customer_id", "product_id", and "quantity", and you want to calculate the total quantity of products ordered by each customer, you can use the following query:

SELECT customer_id, SUM(quantity) AS total_quantity_ordered
FROM (
    SELECT customer_id, product_id, SUM(quantity) AS quantity
    FROM orders
    GROUP BY customer_id, product_id
) AS subquery
GROUP BY customer_id;

This query will calculate the sum of the "quantity" column after grouping the rows by "customer_id" and "product_id" in the subquery. Then, in the outer query, it will group the result by "customer_id" to provide the total quantity ordered by each customer.

 

 

2. 내가 작성한 코드

select sum(sum1), sum(sum2) from
( 
    select G.file_id, G.runtime_gitReset as sum1, G.runtime_editScript as sum2
    from commits as c
    inner join files f
    on c.commit_id = f.commit_id
    inner join changes_GT_3_runtime G on f.file_id = G.file_id
    where c.project_name = 'neo4j'
    group by G.file_id, G.runtime_gitReset, G.runtime_editScript
    order by G.file_id
) as subquery