RE: Get COUNT results from two different columns

[Clive Swan]

Hi David,

 

Thanks for the advice much appreciated.

It is the first time that I have encountered this requirement, so was a bit 
lost in the forest..

 

As I only want a single id and value (new – old), would it be more efficient to 
use a SUBTRACT rather than a UNION functions??

 

The UNION is working now… thanks..

 

 

-- UNION 

SELECT union_subquery.new_sup_id, sum(new_count)

 

FROM

(

                SELECT 

                new_sup AS new_sup_id, COUNT(new_sup) AS new_count

                FROM public."data" 

                GROUP BY new_sup_id

 

                UNION

 

                SELECT old_sup, 

                - COUNT(old_sup) AS old_count

                FROM public."data"

                GROUP BY old_sup

)

AS union_subquery (new_sup_id, new_count)

GROUP BY union_subquery.new_sup_id, union_subquery.new_count

 

 

 

From: David G. Johnston [mailto:david.g.johns...@gmail.com] 
Sent: 23 September 2021 17:45
To: clives...@gmail.com
Cc: pgsql-generallists.postgresql.org
Subject: Re: Get COUNT results from two different columns

 

On Thu, Sep 23, 2021 at 6:37 AM Clive Swan <clives...@gmail.com> wrote:

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?

 

While you finally did provide this info, it is important to be upfront about 
exactly what error you are receiving.

 

Honestly, it feels odd leveraging UNION to solve this problem, but upon further 
reflection it does provide a nice solution.

 

/* an (incomplete) subquery to be substituted into the main query below /*

SELECT id, count(*) AS positive_counts --positive values

UNION ALL

SELECT id, (- count(*)) AS negative_counts --negative of the count

 

You now have a table where IDs (can) repeat, but at most appear only twice, 
once with a positive count and once with a negative count.  All you need to do 
to get your final answer is sum the positive and negative count together for 
each ID.

 

SELECT union_subquery.id, sum(union_subquery.counted)

 

FROM (/*this is a subquery, in the FROM clause */) AS union_subquery (id, 
counted)

 

 -- which requires an alias (name), which your query omitted and why you got 
the error you described.

 

-- I added column aliases here to emphasize that there are only two output 
columns

-- the name of the second column is originally taken from the first unioned 
query

-- (so, positive_counts, the name negative_counts is discarded once the union 
is complete.

-- But since writing sum(positive_counts) in the main query would be confusing 
I renamed

-- the column to just "counted" using the alias clause

 

GROUP BY union_subquery.id

 

David J.