agregate sales totals per years per customer in one transaction-CodePudding

i have a normal turnover table with 3 columns "customer" , "year", "amount" (for example)

customer	year	amount
anton	2020	$5
paul	2019	$12
anton	2021	$5
paul	2019	$10
felicia	2021	$5
anton	2019	$12
felipe	2019	$12

and i have the following mysql query

SELECT `customer` , SUM(`amount`) as summ FROM `customer`.`accountsales` WHERE `amount`> 0 GROUP BY `customer` ORDER BY summ DESC ;

This transaction gives me a nice Paretto table with the sales of each customer in descending order

name	sales all years
customer1	sum of all transactions of customer1
customer2	sum of all transactions of customer2
customer3	sum of all transactions of customer3

so far so good, i want to go one step further and i want to create the following table

Name	Sales all years	Sales 2021	Sales2020	Sales2019
customer1	sum1	sum2021 from customer1	sum2020 from customer1	sum2019 from customer1
customer2	sum2	sum2021 from customer2	sum2020 from customer2	sum2019 from customer2
customer3	sum3	sum2021 from customer3	sum2020 from customer3	sum2019 from customer3

but i want to do it in only one transaction, because the initial table is very huge.

can someone give a hint ?

p.S. feel free to edit the title since I am not very inspired today

CodePudding user response：

You can try to use condition aggregate function

Query 1:

SELECT `customer` , 
       SUM(`amount`) 'Sales all years',
        SUM(CASE WHEN year = 2021 THEN `amount` ELSE 0 END)  Sales2021,
        SUM(CASE WHEN year = 2020 THEN `amount` ELSE 0 END)  Sales2020,
        SUM(CASE WHEN year = 2019 THEN `amount` ELSE 0 END)  Sales2019
FROM accountsales
GROUP BY `customer`

Results:

| customer | Sales all years | Sales2021 | Sales2020 | Sales2019 |
|----------|-----------------|-----------|-----------|-----------|
|    anton |              22 |         5 |         5 |        12 |
|  felicia |               5 |         5 |         0 |         0 |
|   felipe |              12 |         0 |         0 |        12 |
|     paul |              22 |         0 |         0 |        22 |