CODEWITHSUNDEEP
sqlpostgresql
Richard
Richard

Reputation: 65600

SQL: Two queries in a single set of results?

I'm using Postgres 9.6. I have three tables, like this:

Table public.user
id      integer
name    character varying
email   character varying

Table public.project
id      integer
user_id integer 

Table public.sale
id      integer
user_id integer

user_id is a foreign key in both the project and sale tables.

Is there a way I can get a list back of all user IDs with the number of projects and number of sales attached to them, as a single query?

So I'd like final data that looks like this:

user_id,num_projects,num_stories
121,28,1
122,43,6
123,67,2

I know how to do just the number of projects:

SELECT "user".id, COUNT(*) AS num_visualisations 
JOIN project ON project.user_id="user".id
GROUP BY "user".id
ORDER BY "user".id DESC

But I don't know how also to get the number of sales too, in a single query.

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Use subqueries for the aggregation and a left join:

select u.*, p.num_projects, s.num_sales
from user u left join
     (select p.user_id, count(*) as num_projects
      from projects p
      group by p.user_id
     ) p
     on p.user_id = u.id left join
     (select s.user_id, count(*) as num_sales
      from sales s
      group by s.user_id
     ) s
     on s.user_id = u.id;

Upvotes: 1

Related Questions