Postgresql rows to columns (UNION ALL to JOIN)

Question

Hello with this query I'm getting one result with four rows, how can I change it in order to get four named columns with their own result every one?

SELECT COUNT(*) FROM vehicles WHERE cus=1
UNION ALL
SELECT COUNT(*) FROM user WHERE cus=1
UNION ALL
SELECT COUNT(*) FROM vehicle_events WHERE cus=1
UNION ALL
SELECT COUNT(*) FROM vehicle_alerts WHERE cus=1

Thanks in advance.

Politank-Z · Accepted Answer · 2015-06-03 01:46:00Z

5

SELECT a.ct veh_count, b.ct user_count, c.ct event_count, d.ct alert_count
FROM
( SELECT COUNT(*) ct FROM vehicles WHERE cus=1 ) a,
( SELECT COUNT(*) ct FROM user WHERE cus=1 ) b,
( SELECT COUNT(*) ct FROM vehicle_events WHERE cus=1 ) c,
( SELECT COUNT(*) ct FROM vehicle_alerts WHERE cus=1 ) d;

answered Jun 3, 2015 at 1:46

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

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

Comments

user2864740 · Accepted Answer · 2015-06-03 01:49:35Z

2

UNION only adds rows; it has no effect on the columns.

Columns, which define the "shape" of the row tuples, must appear as selected columns¹.

For example:

 SELECT
      (SELECT COUNT(*) FROM vehicles WHERE cus=1) as veh_count
     ,(SELECT COUNT(*) FROM users WHERE cus=1) as user_count
     ..

¹ There are other constructs that can allow this, see crosstab for example - but the columns are fixed by the query command. It takes dynamic SQL to get a variable number of columns.

edited Jun 3, 2015 at 1:49

answered Jun 3, 2015 at 1:44

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

Collectives™ on Stack Overflow

Postgresql rows to columns (UNION ALL to JOIN)

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