SQL Question

Oracle SQL Return Count of Primary key from 2 different columns in the same table

Tables
ARMASTER = Customer Information
ORDERHEAD = Sales order information (Ship to, bill to)

Hey everyone! I'm still quite new to SQL and I do belive I'm picking it up fairly quick. I have been racking my brain on this for a few hours now and have asked around the office and nobody seems to have a solution.

I'm trying to identify how many times a customer account has been used as a SHIP TO location & a BILL TO location.

SELECT ARMASTER.CUSTOMER,
ARMASTER.DIVISION,
ARMASTER.STATUS,
ARMASTER.CUHEAD AS "MASTER",
COUNT (ORDERHEAD.BILLTO) AS "COUNT_BILL",
COUNT (ORDERHEAD.SHIPTO) AS "COUNT_SHIP"

FROM ARMASTER

LEFT OUTER JOIN ORDERHEAD
ON ARMASTER.CUSTOMER = ORDERHEAD.BILLTO
LEFT OUTER JOIN ORDERHEAD
ON ARMASTER.CUSTOMER = ORDERHEAD.SHIPTO

GROUP BY ARMASTER.CUSTOMER,
ARMASTER.DIVISION,
ARMASTER.STATUS,
ARMASTER.CUHEAD


And I'm not even remotley getting what I should be getting. However, when I remove one of my joins the count is exactly what it should be for either 1 or the other of them.

Any guidance would be muchly appreciated! Thank you!

Answer

I think it can also work

    SELECT CUSTOMER,
       DIVISION,
       STATUS,
       CUHEAD AS "MASTER",
       (SELECT COUNT(1) FROM ORDERHEAD WHERE SHIPTO = ARMASTER.CUSTOMER ) AS "COUNT_BILL",
       (SELECT COUNT(1) FROM ORDERHEAD WHERE BILLTO = ARMASTER.CUSTOMER ) AS "COUNT_SHIP"
FROM ARMASTER