DISTINCT / Unique

oracle

I am running a query that I want to return only the distinct patron_id.

SELECT
  pa.patron_account_id,
  pa.patron_account_name,
  pce.email,
  e.event_code AS EVENTCODE,
  sum(po.sales_revenue_amount),
  t.transaction_date
from event_seat es,
     buyer_type bt,
     event e,
     patron_account pa,
     patron_order po,
     sales_rep slsrep,
     season s,
     transaction t,
     patron_contact_address pca,
     patron_contact_email pce
where es.buyer_type_id = bt.buyer_type_id
  and es.event_id = e.event_id
  and es.financial_patron_account_id = pa.patron_account_id
  and es.order_id = po.order_id
  and po.sales_rep_id = slsrep.sales_rep_id (+)
  and e.season_id = s.season_id
  and es.transaction_id = t.transaction_id
  and pa.patron_account_id = pca.patron_account_id
  and pa.patron_account_id = pce.patron_account_id
  and s.season_code = '17ABC'
  and bt.buyer_type_code = 'ADULT'
  and e.event_code = '17ABCSEP23'
  --and e.event_id >= '1000' and e.event_id <= '1234'
  and pca.primary = '1'
  and pce.primary = '1'
group by pa.patron_account_id,
         pa.patron_account_name,
         pce.email,
         e.event_code,
         po.sales_revenue_amount,
         t.transaction_date
order by pa.patron_account_id;

Currently, my results have six instances of duplicates. For example, patron_account_id 123456 has two results, one for a po.sales_revenue_amount of $100 and a second for $80.

Ideally, I want my results to show 1 line item per pa.patron_Account_id with the aggregate po.sales_revenue_amount of $180


SELECT
 patron_account_id, 
 patron_account_name, 
 email, 
 EVENTCODE, 
 sum(sales_amt)

FROM 
  (SELECT 
   DISTINCT pa.patron_account_id,
   pa.patron_account_name,
   pce.email,
   e.event_code                  AS      EVENTCODE,
   sum(po.sales_revenue_amount)  AS      sales_amt

   from
   event_seat es,
   buyer_type bt,
   event e,
   patron_account pa,
   patron_order po,
   sales_rep slsrep,
   season s,
   transaction t,
   patron_contact_address pca,
   patron_contact_email pce

   where
   es.buyer_type_id = bt.buyer_type_id
   and es.event_id = e.event_id
   and es.financial_patron_account_id = pa.patron_account_id
   and es.order_id = po.order_id
   and po.sales_rep_id = slsrep.sales_rep_id (+)
   and e.season_id = s.season_id
   and es.transaction_id = t.transaction_id
   and pa.patron_account_id = pca.patron_account_id
   and pa.patron_account_id = pce.patron_account_id


   and s.season_code = '17123'
   and bt.buyer_type_code = 'ADULT'
   and e.event_code = '17ABCSEP23'
   --and e.event_id >= '5039' and e.event_id <= '5079'
   and pca.primary = '1'
   and pce.primary = '1'


   group by
   pa.patron_account_id,
   pa.patron_account_name,
   pce.email,
   e.event_code,
   po.sales_revenue_amount

   order by
   pa.patron_account_id)

GROUP BY  
patron_account_id, 
patron_account_name, 
email, 
EVENTCODE

ORDER BY 
patron_account_id;

Best Answer

Then you should use the aggregate function SUM:

SELECT pa.patron_account_id, sum(po.sales_revenue_amount)
  FROM SOURCE
 GROUP BY pa.patron_account_id;