-1

I am trying to implement a full outer join query that displays all customers and staff and their sum of all their purchases. It is running without throwing any errors, however, the aggregate sum function isn't working. Only the N/A is being printed, no review scores are in the output. 

SELECT CUSTOMER.C_NAME, STAFF.E_NAME, DECODE(SUM(CUSTOMER.PRICE),
                                    NULL, 'N/A') AS BILL
FROM CUSTOMER
FULL OUTER JOIN STAFF
ON CUSTOMER.C_NAME = STAFF.E_NAME
group by CUSTOMER.C_NAME, E_NAME
order by CUSTOMER.C_NAME, E_NAME;

I want to understand s happening to make this query problematic.

lnjblue
  • 140
  • 1
  • 13
  • Your query makes not sense on many levels. Edit your question and provide sample data and desired results. Why would an employee have more than one row in the `EMP` table? – Gordon Linoff Feb 18 '16 at 22:47
  • I want to understand why you thought `full join` was relevant and what the whole `decode` and N/A thing is for. Without source data and expected results there is no point in readers speculating. And now you've edited the query so it no longer corresponds to the opening paragraph, making even less sense. – underscore_d Feb 18 '16 at 22:56
  • Since the my other query wasn't appropriate, I posted a similar one I'm having trouble with instead. – lnjblue Feb 18 '16 at 23:00

1 Answers1

0

This is about fixing the query in your question, which doesn't make sense to me.

If you want the average of emp.review, then a full outer join is not appropriate. You can probably just use a left outer join:

SELECT e.E_NAME, m.M_NAME, AVG(e.REVIEW) AS REVIEW_SCORE
FROM EMP e LEFT OUTER JOIN
     MANAGER m
     ON e.E_NAME = m.M_NAME
GROUP BY e.E_NAME, m.M_NAME
ORDER BY e.E_NAME, m.M_NAME;

I wouldn't add the N/A in the output. An average is a number. NULL is perfectly reasonable for representing a missing value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786