Skip to content

Instantly share code, notes, and snippets.

@borenstejn

borenstejn/1.3

Created January 13, 2018 17:25
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save borenstejn/6515fd7c8f83ada300c82a5d13cadad2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. borenstejn created this gist Jan 13, 2018.
    18 changes: 18 additions & 0 deletions 1.3
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    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)