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

Revisions

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