Skip to content

Instantly share code, notes, and snippets.

@borenstejn

borenstejn/1.1 et 1.2

Created January 13, 2018 17:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save borenstejn/e2c4c4d429091dc326e3ff003152c5b4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save borenstejn/e2c4c4d429091dc326e3ff003152c5b4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1.1 et 1.2
SELECT
Operations.date,
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(Operations.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 Operations.date
LIMIT @limit OFFSET @limit*(page_number - 1)
@borenstejn
Copy link
Copy Markdown
Author

borenstejn commented Jan 13, 2018

J'ai utilisé les variables @limit et @page_number pour gérer la pagination

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment