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: