CODEWITHSUNDEEP
sqlpostgresqlselect
Blip
Blip

Reputation: 3171

Is it possible to have 1 query instead of 2 queries for the below mentioned case?

I have two tables in Postgresql as below:

  1. receipt

    id CHARACTER VARYING(20),
account CHARACTER VARYING(20),
date DATE,
amount NUMERIC(8,2),
PRIMARY KEY(id)

  2. non_cash

    id CHARACTER VARYING(20),
account CHARACTER VARYING(20),
date DATE,
amount NUMERIC(8,2),
PRIMARY KEY(id)

Now I want to select the SUM(amount) from both the tables where account = '00210_pre' and SUM(amount) and MAX(date) from both the tables where account = '00210_int'. I am able to get the desired result using 2 queries as:

SELECT SUM(amount) AS int_total, MAX(date)
FROM (SELECT amount, date, account FROM non_cash
UNION SELECT amount, date, account FROM receipts) AS v
WHERE account = '00210_int'

and

SELECT SUM(amount) AS pre_total
FROM (SELECT amount, account FROM non_cash
UNION SELECT amount, account FROM receipts) AS v
WHERE account = '00210_pre'

Now My question is Is it possible to have the above in just one query? If yes how?

Kindly note that the account only varies in the suffix as _pre and _int the rest numbers are same.

Upvotes: 0

Views: 65

Answers (2)

Kirk Roybal
Kirk Roybal

Reputation: 17857

SELECT left(nc.account,5) account, 
    max(greatest(nc.date, r.date)) max_date,
    sum(nc.amount + r.amount) total
    sum(CASE WHEN nc.account = '00210_int' OR r.account = '00210_int' THEN 
            nc.amount + r.amount
            ELSE NULL
            END
            ) int_total,
    sum(CASE WHEN nc.account = '00210_pre' OR r.account = '00210_pre' THEN 
            nc.amount + r.amount
            ELSE NULL
            END
            ) pre_total
FROM non_cash nc
INNER JOIN receipts r
ON left(nc.account,5) = left(r.account,5)
WHERE nc.account  IN ( '00210_int', '00210_pre')
OR r.account  IN ( '00210_int', '00210_pre')
GROUP BY 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You shouldn't use UNION. You should use UNION ALL, because UNION removes duplicates, which is probably not desirable and is a waste of effort. But, the answer to your question is GROUP BY:

SELECT account, SUM(amount) AS int_total, MAX(date)
FROM ((SELECT amount, date, account FROM non_cash
      ) UNION ALL
      (SELECT amount, date, account FROM receipts)
     ) v
WHERE account IN ('00210_int', '00210_pre')
GROUP BY account;

Note: if you want the values combined, then don't use the GROUP BY:

SELECT SUM(amount) AS int_total, MAX(date)
FROM ((SELECT amount, date, account FROM non_cash
      ) UNION ALL
      (SELECT amount, date, account FROM receipts)
     ) v
WHERE account IN ('00210_int', '00210_pre');

EDIT:

To get a single row use conditional aggregation:

SELECT SUM(CASE WHEN account = '00210_int' THEN amount ELSE 0 END) AS int_total,
       SUM(CASE WHEN account = '00210_pre' THEN amount ELSE 0 END) AS pre_total,
       MAX(date)
FROM ((SELECT amount, date, account FROM non_cash
      ) UNION ALL
      (SELECT amount, date, account FROM receipts)
     ) v
WHERE account IN ('00210_int', '00210_pre');

Upvotes: 1

Related Questions