Re: Get COUNT results from two different columns

On Thu, Sep 23, 2021 at 6:37 AM Clive Swan <cliveswan(at)gmail(dot)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.