Sum of amount and group by calculations

Help with syntax issue in the group by  I need to create a query that will return the total contract_amount by Supplier Name.  What am I doing wrong?

SELECT DISTINCT B.supplier_legal_name                     supplier_name
      , SUM(NVL( p.blanket_total_amount * NVL(p.rate, 1), 0) ) contract_amount
      , Sum(x.invoiced_amount)                            invoiced_amount
FROM po.po_headers_all p
      Left Outer JOIN   per_all_people_mv             a ON p.agent_id = a.person_id 
      Left Outer JOIN   best_supplier_data_mv         b ON p.vendor_site_id = b.vendor_site_id
      Left Outer Join    dss_agreement_amounts_total_v   x on p.po_header_id = x.po_header_id
WHERE p.type_lookup_code IN ( 'BLANKET', 'CONTRACT' )
      AND TRUNC( NVL( p.start_date, p.approved_date ) ) <= TRUNC(SYSDATE) 
      AND TRUNC( NVL( p.end_date, SYSDATE ) ) >= TRUNC(SYSDATE) 
      AND NVL( p.closed_code, 'OPEN' ) = 'OPEN'
      AND NVL( p.cancel_flag, 'N' ) = 'N'
      AND p.approved_flag IN ( 'Y', 'R' ) 
Group By b.supplier_legal_name
      , SUM(NVL( p.blanket_total_amount * NVL(p.rate, 1), 0))
      , sum(invoiced_amount)
 ORDER BY SUPPLIER_NAME ASC
      ;

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You only group by non-aggregate (columns with no SUM/AVG/MIN/etc...) columns

I'm also pretty sure you cannot use a column alias in the order by.

Note the TRUNC on your date columns will ignore any indexes you might have on them.

You also shouldn't need DISTINCT the way you were using it.

Try this:
SELECT B.supplier_legal_name                     supplier_name
      , SUM(NVL( p.blanket_total_amount * NVL(p.rate, 1), 0) ) contract_amount
      , Sum(x.invoiced_amount)                            invoiced_amount
FROM po.po_headers_all p
      Left Outer JOIN   per_all_people_mv             a ON p.agent_id = a.person_id 
      Left Outer JOIN   best_supplier_data_mv         b ON p.vendor_site_id = b.vendor_site_id
      Left Outer Join    dss_agreement_amounts_total_v   x on p.po_header_id = x.po_header_id
WHERE p.type_lookup_code IN ( 'BLANKET', 'CONTRACT' )
      AND TRUNC( NVL( p.start_date, p.approved_date ) ) <= TRUNC(SYSDATE) 
      AND TRUNC( NVL( p.end_date, SYSDATE ) ) >= TRUNC(SYSDATE) 
      AND NVL( p.closed_code, 'OPEN' ) = 'OPEN'
      AND NVL( p.cancel_flag, 'N' ) = 'N'
      AND p.approved_flag IN ( 'Y', 'R' ) 
Group By b.supplier_legal_name
ORDER BY b.supplier_legal_name ASC
      ;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.