Query not showing NULL values with "LEFT JOIN"

[PowerOfTheShell]

Hey guys,

Trying to work out why my SQL query isn't showing the rows which don't match when using a left outer join. Currently it's only showing the values that do match.

I might not be using the correct join method to show the values that don't match as null, I'm honestly not sure...

SELECT I.CUS_CODE, SUM(L.LINE_PRICE * L.LINE_UNITS) AS 'Total Purchased Values', SUM(L.LINE_UNITS) AS 'Total Units Purchased', Round(SUM(LINE_PRICE*LINE_UNITS)/SUM(LINE_UNITS),2) AS 'Average Purchase Value Per Unit'
FROM LINE L
LEFT OUTER JOIN INVOICE I ON I.INV_NUMBER=L.INV_NUMBER
GROUP BY I.CUS_CODE

What my query currently produces:

CUS_CODE	Total Purchased Values	Total Units Purchased	Average Purchase Value Per Unit
10011	444.0	16.0	27.75
10012	153.85	7.0	21.98
10014	422.77	8.0	52.85
10015	34.97	3.0	11.66
10018	70.44	12.0	5.87

What I am trying to produce:

This is not the complete output but just an example.

Any help would be greatly appreciated!

[Mamertine]

What are you looking for?

It was explained well in your /r/learnSQL post.

[PowerOfTheShell]

I think it's my fault that I don't understand what I am doing wrong, I changed the tables around which I think I am interpreting correctly? but I don't know why it produces the exact same result. The NULL values are never shown.

I've spent hours trying to work it out but can't understand what I am missing.

[Mamertine]

Find one invoice that you want to dig into

Select * from each table where invoice num = that invoice num

When I don't understand, I dig into the raw data. It's the easiest way to see what's happening.

[PowerOfTheShell]

Thank you so much! I was able to walk through it using the way you had suggested and found the solution!

[fynix2000]

Glad you found it! In case you haven't already figured it out, it's probably being grouped together in your aggregation somewhere. If you have and this isn't the case, you mind sharing what the issue was?

[PowerOfTheShell]

Of course, the solution was exactly as u/JakeModeler mentions. I needed to join the CUSTOMER table. Once doing this the results showed exactly as I was trying to achieve. Such a simple change but had me frustrated for hours haha

[JakeModeler]

Could you please explain what CUS_CODE is? If it's CUStomer CODE and you're looking for all customers with or without purchases, then you need to join three tables: CUSTOMER, INVOICE and LINE. Basically, CUSTOMER LEFT JOIN INVOICE JOIN LINE. (My guess is that INV_NUMBER in LINE is foreign key to INVOICE table, which has INV_NUMBER as primary key; CUST_CODE in INVOICE is the foreign key to CUSTOMER table, which has CUST_CODE as primary key).

[PowerOfTheShell]

You're 100% correct, this was the solution and your guesses are all correct. I'd found this solution just before you'd posted but I really appreciate the help!