Обсуждение: RE: Get COUNT results from two different columns
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
On 23/09/2021 14:37, Clive Swan 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? At a quick guess, you'll need to move the subtraction outside the two subqueries - stuff inside either query can't see anything in the other one. Ray. -- Raymond O'Donnell // Galway // Ireland ray@rodonnell.ie
Hi, > I have two separate queries that work individually, returning a count from each column. > I would appreciate any pointers. > new_sup, COUNT(new_sup) AS new_sup_count > old_sup, COUNT(old_sup) AS old_sup_count At least some of your problem is here - in your UNION-ed table, this is one and the same field and therefore cannot havetwo different names. HTH, SQL Padawan
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,
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
Greetings, Thanks for the message. The error message that I get is. ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. Position: 100 Trying to make sense of it.. Clive -----Original Message----- From: SQL Padawan [mailto:sql_padawan@protonmail.com] Sent: 23 September 2021 14:50 To: cliveswan@gmail.com Cc: pgsql-general@lists.postgresql.org Subject: RE: Get COUNT results from two different columns Hi, > I have two separate queries that work individually, returning a count from each column. > I would appreciate any pointers. > new_sup, COUNT(new_sup) AS new_sup_count > old_sup, COUNT(old_sup) AS old_sup_count At least some of your problem is here - in your UNION-ed table, this is one and the same field and therefore cannot havetwo different names. HTH, SQL Padawan
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?
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.johnston@gmail.com]
Sent: 23 September 2021 17:45
To: cliveswan@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 <cliveswan@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.