CODEWITHSUNDEEP
sql
user1985273
user1985273

Reputation: 1967

Query that selects the sum of all records that are referenced in another table

I have two tables, parent(id, name, child_id) and child(id, name, number) - not all parents may have childs and not all childs may have parents. I need a query that selects the sum of all records in child table and also selects the sum of only those records that have a parent and those that dont - that is determined by parent tables child_id column. How can this be done?

select
  sum(c.number) AS sum AS a,
  sum(all_child_records_that_have_a_parent) AS b,
  sum(all_child_records_that_do not have a parent) AS c /*do not use a-b if possible*/
from
  child c

The "all_child_records_that_have_a_parent" is the one i cant figure out :)

Upvotes: 0

Views: 34

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

You can select distinct child ids from the parent table and outer join these to your child table. Then check for NULL.

select
  sum(c.number) AS sum_all_c,
  sum(case when x.child_id is not null then c.number end) AS sum_c_with_parent,
  sum(case when x.child_id is null then c.number end) AS sum_c_without_parent
from child c
left outer join (select distinct child_id from parent) x on x.child_id = c.id;

Upvotes: 1

Paul Facklam
Paul Facklam

Reputation: 1643

all_child_records_that_do not have a parent:

SELECT *
  FROM child
 WHERE id NOT IN (SELECT child_id FROM parent)

Upvotes: 2

Related Questions