Re: Get COUNT results from two different columns - Mailing list pgsql-general

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