CODEWITHSUNDEEP
sqlpostgresql
Nick Dubus
Nick Dubus

Reputation: 91

POSTGRESQL - count occurences of value in another table with regard to another value, even if = 0

quick disclaimer, THIS IS FOR AN ASSIGNMENT.

The question itself is:

List each credit limit that is held by more than one customer, together with the number of customers of Johnny Smith who have this limit

There are 5 tables in my schema, the relevant ones are (not complete, just relevant info):

TableCUSTOMER C

C.CREDIT_LIMIT

C.REP_NUM REFERENCES REP

(i.e. the table name is Customer, has columns credit_limit and rep_num, which is a foreign key to the "rep" table.)

Table REP R

R.REP_NUM

R.NAME (i.e. table name is REP, reps have a number and a name).

The rep in question's number is 20.

My current attempt is this: 

SELECT COUNT(C.REP_NUM), C.CREDIT_LIMIT
FROM CUSTOMER C
WHERE C.REP_NUM = '20'
GROUP BY  C.CREDIT_LIMIT
HAVING COUNT(C.CREDIT_LIMIT) >= 2;

Which is almost working. The only issue is that there are two repeating credit limits, one where the rep has customers in it, and one where it doesn't. The one where he has customers is showing up, and the other isn't.

To sum it up, rather than getting: 

Occurences - Credit_Limit
3          - 7500
0          - 10,000

I'm getting 

Occurences - Credit Limit 
3          - 7500

How would I go about making that other tuple show up?

Upvotes: 0

Views: 63

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51559

use left outer join, eg:

select 
* 
from a
left outer join b on a.id=b.id

so if there is two rows in and one row in b, will get two rows with left half empty for b part...

Upvotes: 1

Related Questions