rayne rayne -3 years ago 107
SQL Question

Default row for SQL

DB - Oracle

create table customer_exercise(
customer_id number,
exercise_id number,
cnt number,
exercise_date date)


Data

1000 10 3 14-AUG-17
1001 20 6 14-AUG-17
1000 20 2 14-AUG-17


Is it possible to get a default row when the record does not exist for the condition in in clause?

select customer_id, exercise_id, sum(cnt)
from customer_exercise
where customer_id in (1000, 1001, 1003)
and exercise_id in (10, 20)
group by customer_id, exercise_id
order by sum(cnt)


result of above query-

1000 20 2
1000 10 3
1001 20 6


Since customer-ids in the in clause may not have a record for certain exercise-ids, is it possible using SQL to get a result like the one below with sum as 0 for those? For e.g. 1001 does not have a record for exercise-id=10, so sum will be 0.

1001 10 0
1003 10 0
1003 20 0
1000 20 2
1000 10 3
1001 20 6

Answer Source

You could turn your in clause conditions into collections (such as a built-in collection type, handy for this sort of thing), expand them into relational data in CTEs, and then cross-join them; and left-join to the real table to see what matches:

with customer_cte (customer_id) as (
  select * from table(sys.odcinumberlist(1000, 1001, 1003))
),
exercise_cte (exercise_id) as (
  select * from table(sys.odcinumberlist(10, 20))
)
select c.customer_id, e.exercise_id, coalesce(sum(ce.cnt), 0) as total_cnt
from customer_cte c
cross join exercise_cte e
left join customer_exercise ce
on ce.customer_id = c.customer_id
and ce.exercise_id = e.exercise_id
group by c.customer_id, e.exercise_id
order by coalesce(sum(cnt), 0), customer_id, exercise_id
/

CUSTOMER_ID EXERCISE_ID  TOTAL_CNT
----------- ----------- ----------
       1001          10          0
       1003          10          0
       1003          20          0
       1000          20          2
       1000          10          3
       1001          20          6

6 rows selected. 

If you already do have separate customer and exercise tables, and they contain at least all the IDs you're looking for, then you can use those directly instead, and filter against them instead of your mapping table:

select c.customer_id, e.exercise_id, coalesce(sum(ce.cnt), 0) as total_cnt
from customer c
cross join exercise e
left join customer_exercise ce
on ce.customer_id = c.customer_id
and ce.exercise_id = e.exercise_id
where c.customer_id in (1000, 1001, 1003)
and e.exercise_id in (10, 20)
group by c.customer_id, e.exercise_id
order by coalesce(sum(cnt), 0), customer_id, exercise_id

You won't get default rows for any IDs that don't exist in the customer and exercise tables this way, but that may not be an issue.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download