SELECT c.id customer_id,COUNT(DISTINCT CASE WHEN tsl.line_discount_amount>0 THEN t.id END) promo_purchases,COUNT(DISTINCT CASE WHEN tsl.line_discount_amount=0 THEN t.id END) regular_purchases FROM contacts c JOIN transactions t ON c.id=t.contact_id JOIN transaction_sell_lines tsl ON t.id=tsl.transaction_id WHERE t.type="sell" GROUP BY c.id;