O
If you have a SaleId, count it. count(distinct SaleId) Instead of a simple number of records:declare @sale table (saleId int, customerId int, price float)
declare @saleCat table (saleId int, categoryId int)
insert into @sale values
(1, 1, 100), (2, 1, 200),
(3, 2, 300), (4, 2, 400),
(5, 3, 500)
insert into @saleCat values
(1, 1),
(2, 1), (2, 2),
(3, 1), (3, 2), (3, 3),
(4, 2), (4, 3),
(5, 3)
select
s.customerId,
sc.categoryId,
numSales = count(distinct s.saleId)
from
@sale s
join @saleCat sc on sc.saleId = s.saleId
group by
cube (s.customerId, sc.categoryId)
order by
grouping(s.customerId), s.customerId,
grouping(sc.categoryId), sc.categoryId
Result:customerId categoryId numSales
1 1 2
1 2 1
1 NULL 2
2 1 1
2 2 2
2 3 2
2 NULL 2
3 3 1
3 NULL 1
NULL 1 3
NULL 2 3
NULL 3 3
NULL NULL 5
If you need a sum, it's getting a little more complicated. In this case, a separate amount can be grouped into columns where addication is observed and separately where it is not respected. Then connect the two results to the respective ids and flags of the group, selecting the sum of the first or second results:;with custSales (gCust, custId, sales)
as (
select
grouping(s.customerId), s.customerId,
sum(s.price)
from @sale s
group by rollup (s.customerId)
),
custCatSales (gCust, custId, gCat, catId, sales)
as (
select
grouping(s.customerId), s.customerId,
grouping(sc.categoryId), sc.categoryId,
sum(s.price)
from
@sale s
join @saleCat sc on sc.saleId = s.saleId
group by grouping sets (
(s.customerId),
(sc.categoryId),
(s.customerId, sc.categoryId))
)
select
customerId = ccs.custId,
categoryId = ccs.catId,
sales = iif(ccs.gCat = 0, ccs.sales, cs.sales)
from
custCatSales ccs
full join custSales cs on cs.custId = ccs.custId and cs.gCust = ccs.gCust
order by
isnull(ccs.gCust, 2), ccs.custId,
ccs.gCat, ccs.catId
Result:customerId categoryId sales
1 1 300
1 2 200
1 NULL 300
2 1 300
2 2 700
2 3 700
2 NULL 700
3 3 500
3 NULL 500
NULL 1 600
NULL 2 900
NULL 3 1200
NULL NULL 1500
