[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

Enquirer: OMG

||

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);
Respondent: OMG

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
Respondent: Gordon Linoff

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.