Remove part of query if a tuple contains null-CodePudding

create table customers (
id int primary key,
first_name varchar2(30) not null,
addresses varchar2(30)
);

create table items (
item_id int primary key,
seller varchar2(30) not null,
description varchar2(30) not null,
weight int,
price numeric(100,2),
quantity int not null
);

create table bought (
id int,
item_id int,
bdate date,
primary key id, item_id, bdate),
foreign key (id) references customers,
foreign key (item_id) items
);

oracle query - displays the id, first_name, addresses, and total price that each customers spent on buying for items. If a customer bought an item that has a null price, change the total price to null even if the other items that particular customer bought doesn't have a null price.

I can do the 1st part

SELECT c.id, c.first_name, c.addresses, SUM(i.price) as total_price
FROM customers c 
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id

But how do I change the total_price to null if I detect a null?

CodePudding user response：

Most aggregate functions will ignore nulls, so you need to do a NULL check and then aggregate the result. If the count of nulls is greated than 0, show null, else show the total price

SELECT c.id, c.first_name, c.addresses, 
       case when count(case when i.price is null then 1 end) > 0
        then null else SUM(i.price) end as total_price
FROM customers c 
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id

CodePudding user response：

Gary Myers is absolutely correct, but I would write it a bit differently:

SELECT c.id, c.first_name, c.addresses, 
       case 
          when count(i.price)<count(*)
             then null 
          else SUM(i.price) 
       end as total_price
FROM customers c 
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id

or with oracle-specific function decode:

SELECT
   c.id, c.first_name, c.addresses, 
   decode(count(i.price),count(*),SUM(i.price)) as total_price
FROM customers c 
JOIN bought b ON b.id=c.id
JOIN items i ON i.item_id=b.item_id
GROUP BY c.id