RE: Get COUNT results from two different columns

[Clive Swan]

Greetings,

I have two separate queries that work individually, returning a count from
each column.

 

I want to subtract New(COUNT) from Old(Count)

I get an error when trying to run UNION?

 

I would appreciate any pointers.

 

 

-- COUNT NEW SUPPLIER

--

SELECT new_sup,

COUNT(new_sup)

FROM public."Data"

GROUP BY new_sup

 

This returns

Company_D 35

Company_E 30

Company_F 30

 

 

SELECT newld_sup,

COUNT(old_sup)

FROM public."Data"

GROUP BY old_sup

 

This returns

Company_A 1

Company_B 2

Company_D 35

Company_E 30

Company_F 30

 

 

 

SELECT new_sup,  COUNT(new_sup) AS new_sup_count,

old_sup,  COUNT(old_sup) AS old_sup_count 

FROM

(

                SELECT 

    new_sup,  COUNT(new_sup) AS new_sup_count

    FROM public."Data"

    GROUP BY new_sup

    

    UNION

    

    SELECT

    old_sup,  COUNT(old_sup) AS old_sup_count

    FROM public."Data"

    GROUP BY old_sup

   

    new_sup_count - old_sup_count

                )

GROUP BY new_sup