Sum of alias columns in PostgreSQL

Question

I have a query aggregating three result columns:

pending_with_documents
pending_without_documents
inprocessing

SELECT COUNT(DISTINCT (CASE
                           WHEN status_code IN ('ACK', 'INT') THEN
                               CASE
                                   WHEN ack_no IN
                                        (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
    END))        AS pending_with_documents,
       COUNT(DISTINCT (CASE
                           WHEN status_code IN ('ACK', 'INT') THEN
                               CASE
                                   WHEN ack_no NOT IN
                                        (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
           END)) AS pending_without_documents,
       COUNT(CASE
                 WHEN status_code NOT IN ('ACK', 'INT', 'APR', 'REJ', 'SBK', 'OBJ') THEN
                     ack_no
           END)  AS Inprocessing
FROM application_ht_install
WHERE service_code IN (36);

Now I need a fourth column total summing up these three. So I tried:

SELECT COUNT(DISTINCT (CASE
                           WHEN status_code IN ('ACK', 'INT') THEN
                               CASE
                                   WHEN ack_no IN
                                        (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
    END))                             AS pending_with_documents,
       COUNT(DISTINCT (CASE
                           WHEN status_code IN ('ACK', 'INT') THEN
                               CASE
                                   WHEN ack_no NOT IN
                                        (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
           END))                      AS pending_without_documents,
       COUNT(CASE
                 WHEN status_code NOT IN ('ACK', 'INT', 'APR', 'REJ', 'SBK', 'OBJ')
                     THEN ack_no END) AS Inprocessing
    SUM((COUNT(DISTINCT(CASE WHEN status_code IN ('ACK','INT') THEN
    CASE WHEN ack_no IN
    (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
    END)) AS pending_with_documents )+ ( COUNT(DISTINCT(CASE WHEN status_code  IN ('ACK','INT') THEN
    CASE WHEN ack_no NOT IN
    (SELECT ack_no FROM bescom_appl_upload_doc) THEN ack_no END
    END)) AS pending_without_documents)+ ( COUNT(CASE WHEN status_code NOT IN('ACK','INT','APR','REJ','SBK','OBJ') THEN
    ack_no
    END) AS Inprocessing))
FROM application_ht_install
WHERE service_code IN (36);

But I'm getting an error:

ERROR:  syntax error at or near "sum"
LINE 13: sum((count(distinct(case when status_code in ('ACK','INT') t...
              ^ SQL state: 42601 Character: 473

How to add up these three derived columns?

Write an outer query where your query is a subquery in the `FROM` list, and add the columns in thatbouter query? — Laurenz Albe, May 21 '21 at 15:08
Start with actual, precise table definitions (`CREATE TABLE` statements) - it matters! (If any NULL values are involved, `NOT IN` may not be doing what you think it's doing ...) And always your Postgres version. — Erwin Brandstetter, May 21 '21 at 15:40

score 0 · Answer 1 · answered May 21 '21 at 15:14

You have several syntax errors in your statement. You were aliasing things that didn't need aliasing. Here's the code with the syntax errors corrected. Note: One column had no alias so I called it "do_not_know_name" because I cannot tell what you meant.

select 
count(distinct(case when status_code in ('ACK','INT') then 
case when ack_no in
(select ack_no from bescom_appl_upload_doc) then ack_no end 
end)) as pending_with_documents ,
count(distinct(case when status_code  in ('ACK','INT') then 
case when ack_no not in
(select ack_no from bescom_appl_upload_doc) then ack_no end 
end)) as pending_without_documents ,
count(case when status_code not in('ACK','INT','APR','REJ','SBK','OBJ') 
then ack_no end) as Inprocessing,
sum((count(distinct(case when status_code in ('ACK','INT') then 
case when ack_no in
(select ack_no from bescom_appl_upload_doc) then ack_no end 
end))  )+ ( count(distinct(case when status_code  in ('ACK','INT') then 
case when ack_no not in
(select ack_no from bescom_appl_upload_doc) then ack_no end 
end)) )+ ( count(case when status_code not in('ACK','INT','APR','REJ','SBK','OBJ') then 
ack_no
end) )) as do_not_know_name
from application_ht_install where service_code in (36)

I see a couple of nested aggregate calls here in his query, I think Nested aggregate calls are not allowed. `sum((count(distinct(case` and two of `)) )+ ( count(distinct`. Isn't it right? — 0xdw, May 21 '21 at 15:21
@dinidu: Not 100% sure, I don't have the DDL for the tables with data in them, but at least we got past the syntax errors. Was hoping that OP would try this and see if it works or not, or come back with more feedback-- without knowing the data, it's difficult to suggest an alternative without more information. Figured this was the easiest way to get more. — Joe Love, May 21 '21 at 18:12
Also, you can technical have nested aggregates but only if the resolve to a single value prior to being ran... aka: select sum((select avg(some_column) from some_other_source)) from some_table — Joe Love, May 21 '21 at 19:27

Erwin Brandstetter · Answer 2 · 2021-05-21T16:21:55.860

Basically, you can either repeat your lengthy expressions, or put everything in a subquery and sum in an outer SELECT. I suggest the later:

SELECT pending_with_documents
     , pending - pending_with_documents AS pending_without_documents
     , inprocessing
     , pending + inprocessing AS total
FROM  (
   SELECT count(DISTINCT a.ack_no) FILTER (WHERE status_code IN ('ACK', 'INT'))              AS pending
        , count(DISTINCT b.ack_no) FILTER (WHERE status_code IN ('ACK', 'INT'))              AS pending_with_documents
        , count(ack_no) FILTER (WHERE status_code <> ALL ('{ACK, INT, APR, REJ, SBK, OBJ}')) AS inprocessing
   FROM   application_ht_install a
   LEFT   JOIN (SELECT DISTINCT ack_no FROM bescom_appl_upload_doc) b USING (ack_no)
   WHERE  service_code = 36
   ) sub;

This might do what you are after. Depends on actual table definitions. If ack_no in either table or status_code can be NULL, you probably need to do more. See:

Select rows which are not present in other table

Also, the LEFT JOIN to a subquery may or may not be more efficient, depending on undisclosed schema details and cardinalities.

<> ALL (<array>) is equivalent to NOT IN (<list>), but shorter for longer lists and typically performs better. Both fail when NULL is involved ...

About the more efficient FILTER clause replacing the nested CASE expressions:

Aggregate columns with additional (distinct) filters

Note the use of a.ack_no, b.ack_no, and ack_no in the inner SELECT list. a.ack_no is really equivalent to just ack_no after left-joining with a USING clause. I only spelled it out for symmetry.

count(DISTINCT b.ack_no) works, because count() does not count NULL values - which you get for b.ack_no if not present in bescom_appl_upload_doc.

Thank you so much sir... You saved me, still I find hard in understanding your simplified code. But I'll get to understand. Once again thank you so for your time in helping me out. suggest me some fast way to learn postgresql, if you have any. — krickX, May 21 '21 at 17:09
@krickX: I am no expert for that particular question as I started with Postgres in the last millennium. The excellent manual and stackoverflow should certainly help. — Erwin Brandstetter, May 21 '21 at 17:26

Sum of alias columns in PostgreSQL

2 Answers2