Using SQL how do I group by a field then return totals for another fields for each month and the % change each month?

  Kiến thức lập trình

I have a table called Accounts with the fields Name (string), Month (int), Amount (int).

I want to write a query groups by the Name. Then returns columns for each month showing how much the total Amount for that month was. Then calculate the % difference between each month.

I am not doing this in an application so there is no client. After the SQL query the data is written into a Google Sheet. Would it be easier to format the data using an Apps Script.

I wrote this query, but I would eventually like to also add in the year and this would get really long. Is there a cleaner way to do this?

SELECT
    Name,
    SUM(CASE WHEN month = 'January' THEN amount ELSE 0 END) AS January,
    SUM(CASE WHEN month = 'February' THEN amount ELSE 0 END) AS February,
    SUM(CASE WHEN month = 'March' THEN amount ELSE 0 END) AS March,
    SUM(CASE WHEN month = 'April' THEN amount ELSE 0 END) AS April,
    SUM(CASE WHEN month = 'May' THEN amount ELSE 0 END) AS May,
    SUM(CASE WHEN month = 'June' THEN amount ELSE 0 END) AS June,
    SUM(CASE WHEN month = 'July' THEN amount ELSE 0 END) AS July,
    SUM(CASE WHEN month = 'August' THEN amount ELSE 0 END) AS August,
    SUM(CASE WHEN month = 'September' THEN amount ELSE 0 END) AS September,
    SUM(CASE WHEN month = 'October' THEN amount ELSE 0 END) AS October,
    SUM(CASE WHEN month = 'November' THEN amount ELSE 0 END) AS November,
    SUM(CASE WHEN month = 'December' THEN amount ELSE 0 END) AS December
FROM
    your_table_name
GROUP BY
    Name;

LEAVE A COMMENT