Summary
declare @Customer table (customer_id int identity(1,1) not null,name varchar(255),reseller_flag varchar(255))
declare @Order table (order_id int identity(1,1) not null,order_item_id int,customer_id int, [date] date, product_id int)
declare @Product table (product_id int identity(1,1) not null,name varchar(255),category varchar(255))
insert into @Customer(name,reseller_flag)
values
('Lee','NO')
,('Mark','Yes')
insert into @Product(name,category)
values
('Hair Gel','BodyCare')
,('Shampoo','BodyCare')
,('Soap','BodyCare')
,('Sweet','Nutrition')
insert into @Order(order_item_id,customer_id,[date],product_id)
values
(101,1,getdate(),4)
,(102,1,dateadd(day,-1,getdate()),2)
,(103,2,dateadd(day,-3,getdate()),1)
,(104,2,dateadd(day,-10,getdate()),2)
,(105,2,dateadd(day,-15,getdate()),3)
,(106,2,dateadd(day,-17,getdate()),4)
--select * from @Customer
--select * from @Product
--select * from @Order
Select
category
,Total
,convert(varchar(10),round(convert(float,Total)/convert(float,Tot)*100,2)) + '%' as [Percentage]
from(
select
category
,Tot
,count(*) as Total
from @Order a
left join @Product b on a.product_id = b.product_id
left join @Customer c on a.customer_id = c.customer_id
cross apply (
select
sum(case when reseller_flag = 'Yes' then 1 else 0 end) as Tot
from @Order x
left join @Customer cx on x.customer_id = cx.customer_id
where reseller_flag = 'Yes'
)_
where reseller_flag = 'Yes'
group by
category
,Tot
)tbl