-
-
Save borenstejn/6515fd7c8f83ada300c82a5d13cadad2 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| EXTRACT(year from Operations.date) as 'Année', | |
| EXTRACT(MONTH from Operations.date) as 'Mois' | |
| COUNT(distinct Operations.OrderID) as "nombre d'opérations", | |
| SUM(CASE WHEN Operations.type = 'buy' THEN Operations.amount ELSE 0 END) as 'Sous total achat', | |
| SUM(CASE WHEN Operations.type = 'sell' THEN Operations.amount ELSE 0 END) as 'Sous total vente', | |
| SUM(CASE WHEN Payments.method = 'card' THEN Payments.amount ELSE 0 END) as 'Sous total card', | |
| SUM(CASE WHEN Payments.method = 'sepa' THEN Payments.amount ELSE 0 END) as 'Sous total sepa', | |
| SUM(CASE WHEN Payments.method = 'cash' THEN Payments.amount ELSE 0 END) as 'Sous total cash' | |
| SUM(Payments.amount) as 'Total Amount', | |
| SUM(Operations.commission) as 'Commission', | |
| SUM(Operations.amount) - SUM(CASE WHEN Operations.type = 'buy' THEN Opearations.amount ELSE 0 END) + SUM(CASE WHEN Operations.type = 'sell' THEN Opearations.amount ELSE 0 END) as 'amount traded' | |
| FROM Payments | |
| INNER JOIN Operations | |
| WHERE Operations.id = Payments.operation_id AND Operations.status LIKE 'completed' | |
| GROUP BY EXTRACT(year from Operations.date), EXTRACT(MONTH from Operations.date) | |
| LIMIT @limit OFFSET @limit*(@page_number - 1) |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
J'ai donc utilisé les EXTRACT de SQL pour grouper par mois