[Solved] Customer product sale query incorrect result in sql server 2016
My DDL looks like below:
CREATE TABLE CUSTOMER
(
ID INT PRIMARY KEY,
CUSTOMER_NAME VARCHAR(50),
CITY_ID INT,
)
CREATE TABLE product
(
id int,
sku VARCHAR(50),
product_name varchar(100),
stock_qty int
)
create table Invoice
(
id int,
invoice_number varchar(100),
customer_id int
)
CREATE TABLE Invoice_item
(
id int,
invoice_id int,
product_id int,
quantity decimal(5,2),
price decimal(5,2),
line_total_price decimal(5,2)
)
I am trying to get sales details of all customer and products
output should return all customer even customer without invoices and
also all product even those product that were not sold.
I need to print customer even not have invoice and even those product
that were not sold
than customer and product as NA and quantity as o
Code i have written:
SELECT ISNULL(c.customer_name,'N/A')AS customer_name,ISNULL(p.product_name,'N/A') AS product_name,
sum(ISNULL(invitm.quantity,'0')) as quantity
FROM customer as c left outer join product as p
on c.id = p.id
left outer join invoice as inv on c.id = inv.id
left outer join invoice_item as invitm on c.id = invitm.id
group by c.customer_name,p.product_name
But this is giving incorrect result. am i doing any mistake with join. please share your suggestion
Solution #1:
If you want all customer and product combinations, then I would suggest:
select c.customer_name, p.product_name,
coalesce(sum(ii.quantity), 0) as quantity
from customer c cross join
product p left join
invoice i
on c.id = i.customer_id left join
invoice_item ii
on ii.invoice_id = i.id and ii.product_id = p.id
group by c.customer_name, p.product_name;
If you want all customer/product combinations that exist and then extras for the customers and products that don’t exist, I would suggest union all:
select c.customer_name, p.product_name,
coalesce(sum(ii.quantity), 0) as quantity
from invoice i join
customer c
on c.id = i.customer_id join
invoice_item ii
on ii.invoice_id = i.id join
product p
on ii.product_id = p.id
group by c.customer_name, p.product_name
union all
select c.customer_name, null, 0
from customer c
where not exists (select 1 from invoice i where i.customer_id = c.id)
union all
select null, p.product_name, 0
from product p
where not exists (select 1 from invoice_item ii where ii.product_id = p.id);
Solution #2:
Maybe like this (I have not checked syntax) – all your joins were totally wrong; you need to join on corresponding fields (foreign keys), not on id-s:
SELECT ISNULL(c.customer_name,'N/A')AS customer_name,ISNULL(p.product_name,'N/A') AS product_name,
sum(ISNULL(invitm.quantity,'0')) as quantity
FROM customer as c full outer join product as p
on c.product_id = p.id
left outer join invoice as inv on c.id = inv.customer_id
left outer join invoice_item as invitm on invitm.invoice_id = inv.id and invitm.product_id = p.id
group by c.customer_name,p.product_name