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?
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

0

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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.