gistfile1.sql

SELECT DISTINCT ON (transaction)
        settlement,
        uka_transaction_id,
        uka_project_id,
        transaction,
        transaction_type,
        transaction_sum,
        CASE
                WHEN transaction_type = 3 THEN transaction_sum - count(ticket) * -15
                ELSE transaction_sum - count(ticket)*15
                END AS sum,
        CASE
                WHEN transaction_type = 3 THEN count(ticket) * -15
                ELSE count(ticket)*15
                END AS billettavgift,
        payment_method,
        TO_CHAR(DATE_TRUNC('day', settlement_starttime), 'YYYY-MM-DD') AS oppgjorsdato,
        approved,
        count(ticket.refunded) as ticket_refund_count,
        count(ticket) - count(ticket.refunded) as ticket_sold_count
FROM transaction
        JOIN ticket_transaction USING (transaction)
        JOIN ticket USING (ticket)
        JOIN settlement USING (settlement)
        JOIN price_group USING (price_group)
        JOIN ticket_group USING (ticket_group)
        LEFT JOIN uka_project USING (event)
        WHERE
            settlement.settlement_endtime IS NOT NULL
        AND settlement.approved IS NOT NULL
        AND settlement.uka_transaction_id IS NOT NULL
        AND settlement.settlement = 468
group by transaction.settlement, uka_transaction_id, uka_project_id, transaction, transaction_sum, payment_method, settlement_starttime, settlement_endtime, approved, transaction_type;