postgresql run multiple select statements with different columns in single query

Question

I have 14 queries, such as

select activities.type, sum(activity_fees.amount) 
from activity_fees inner join activities 
on activity_fees.activity_id = activities.id 
where DATE(activities.due_at) = current_date - INTERVAL '1 day' 
group by activities.type

SELECT avg(activities.rating) 
FROM fellows 
inner join auth on a.a_id = f.id 
inner join activities on activities.fellow_id = fellows.id  
WHERE f.type in ('x', 'y', 'z') 
and auth.deactivated = false 
and DATE(activities.due_at) = current_date - INTERVAL '1 day' 
and activities.rating is not null

I am trying to run all the queries at once using a GUI. UNION and UNION ALL can only be used when the no of columns are same in the queries?

When I was using Toad I could run the sql queries if I include a delimiter such as ;

I am not sure how this can be done in postgresql?

Thank you.

Sry, I previously was using Toad for Oracle, mysql and switched to postgre, so I assumed users with similar experience might help. — user1940212
– user1940212, Commented Jun 19, 2015 at 22:39
@user1940212 my solution solves the question, if it didn't answer your question that's fine. But if it helped provide you with the solution then accepting it would be a common courtesy. Thanks and good luck! — Philip Devine
– Philip Devine, Commented Jun 22, 2015 at 17:59

Philip Devine · Accepted Answer · 2015-06-18 22:49:27Z

1

Just pad the columns you don't have and union. For instance:

select activities.type, sum(activity_fees.amount) 
...
Union
SELECT 'dummy', avg(activities.rating) 
....

Or just include activities.type since you have it available!

answered Jun 18, 2015 at 22:49

Philip Devine

1,1675 silver badges11 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2015-06-18 23:10:19Z

First, you probably don't need to run 14 different sets of queries. If I had to guess, what changes is the interval. I would suggest that you ask another question about how to simplify your overall process.

If you want to bring results together using union all -- and union all is what you want for this -- then you need the same columns. In addition, the date arithmetic can be optimized.

You seem to have three columns in your data, so I would write this as:

select a.type, sum(af.amount) as amount, NULL as rating
from activity_fees af inner join
     activities a
     on af.activity_id = a.id 
where a.due_at >= current_date - interval '1 day' and
      a.due_at < current_date
group by a.type
union all
select NULL as type, NULL as amount, avg(au.rating)  as rating
from fellows f inner join
     auth au
     on au.a_id = f.id inner join
     activities a
     on a.fellow_id = f.id  
where f.type in ('x', 'y', 'z') and
      au.deactivated = false and
      a.rating is not null and
      (a.due_at >= current_date - INTERVAL '1 day' and
       a.due_at < current_date
      );

Collectives™ on Stack Overflow

postgresql run multiple select statements with different columns in single 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