Re: Get COUNT results from two different columns

[Marc Olivé <marc.olive@xxxxxxxx>]

Seems to me that's not an UNION, but a JOIN:

SELECT o.old_count - n.new_count, o.old_sup, n.new_sup

FROM (

    SELECT new_sup, COUNT(new_sup)

    FROM public."Data"

    GROUP BY new_sup
) n
JOIN (

    SELECT old_sup, COUNT(old_sup)

    FROM public."Data"

    GROUP BY old_sup

) o ON o.old_sup = n.new_sup -- I'm gessing this is the join condition you want

;

Regards,

El jue, 23 sept 2021 a las 15:37, Clive Swan (<cliveswan@xxxxxxxxx>) escribió:

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