performing math inside PostgreSQL query

Question

I need to query three values from two tables. the first two values are queried as follows:

SELECT
    (SELECT COUNT(*) FROM table1) AS count,
    (SELECT COUNT(*) FROM table2 WHERE config IS NULL) AS upCount,

the third value downCount should be count - upCount. can this operation be done by psql and returned as downCount?

Tim Biegeleisen · Accepted Answer · 2019-06-10 11:03:24Z

1

One option would be to simply repeat the subqueries:

SELECT
    (SELECT COUNT(*) FROM table1) AS count,
    (SELECT COUNT(*) FROM table2 WHERE config IS NULL) AS upCount,
    (SELECT COUNT(*) FROM table1) -
        (SELECT COUNT(*) FROM table2 WHERE config IS NULL) AS downCount;

You could also use a CTE to compute the original two subqueries first:

WITH cte AS (
    (SELECT COUNT(*) FROM table1) AS count,
    (SELECT COUNT(*) FROM table2 WHERE config IS NULL) AS upCount
)

SELECT
    count,
    upCount,
    count - upCount AS downCount
FROM cte;

answered Jun 10, 2019 at 11:03

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-06-10 11:02:33Z

0

You can use a subquery or move the subqueries to the FROM clause. I would suggest the latter:

SELECT t1.cnt, t2.upcount, (t1.cnt - t2.upcount) as downcount
FROM (SELECT COUNT(*) as cnt FROM table1) t1 CROSS JOIN
     (SELECT COUNT(*) as upcount FROM table2 WHERE config IS NULL) t2;

answered Jun 10, 2019 at 11:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

performing math inside PostgreSQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related