Re: Get COUNT results from two different columns

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(at)gmail(dot)com>)
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
>