CODEWITHSUNDEEP
sqlsql-servergroup-bysql-server-2008-r2sum
Timothy Carter
Timothy Carter

Reputation: 43

sum with conditional grouping

I have 2 tables: Departments and Sales. In my department table I have a labor department id and the id of the sales department it is associated to. I also want to allow a department's sales to "rollup" up to another department. I need help with the query to "rollup" the sales.

Here are the tables:

TABLE = Departments
LaborDeptid | AssociatedSalesDept | RollUpTo
1                    101              0   
2                    102              0
3                    103              1
4                    104              0

TABLE = Sales
Date      | Sales | SalesDept
1/1/2014    10.00     101 
1/1/2014    10.00     101
1/1/2014    10.00     102
1/1/2014    10.00     102
1/1/2014    10.00     103
1/1/2014    10.00     103
1/1/2014    10.00     104
1/1/2014    10.00     104

Here is the output I would like:

OUTPUT
Date      | LaborDept | TotalSales
1/1/2014       1         40.00
1/1/2014       2         20.00
1/1/2014       4         20.00

As you can see, labor department 1 includes sales for sales department 101 and 103. I have no idea how to do this, though. The query to sum by day, by department is easy enough:

select 
    Date,
    LaborDept,
    sum(sales) as TotalSales
from sales s
inner join departments d on s.SalesDept = d.AssociatedSalesDept 
group by Date,LaborDept`

but how would I do the "rollup"? I tried putting a case statement in the join like so:

select 
    sum(sales) as TotalSales,
    Date,
    LaborDept
from sales s
inner join departments d on s.SalesDept = case when d.RollUpTo <> 0 then 
(select AssociatedSalesDept 
from departments
where d.RollUpTo = LaborDeptID)
else d.AssociatedSalesDept end
group by Date,LaborDept

but that just dropped the 103 sales department all together. And it doesn't seem the right approach.

Upvotes: 3

Views: 129

Answers (3)

drneel
drneel

Reputation: 2907

    SELECT s.Date, 
           d.LaborDeptId,
           SUM(Sales) + ISNULL((SELECT 
                            CASE Departments.RollUpTo
                                WHEN 0 THEN SUM(0)
                                ELSE SUM(Sales)
                            END
                          FROM Departments
                    INNER JOIN Sales ON Departments.AssociatedSalesDept = Sales.SalesDept
                         WHERE Departments.RollUpTo = d.LaborDeptId
                           AND Departments.RollUpTo <> 0
                      GROUP BY Departments.RollUpTo), 0) AS TotalSales
      FROM Departments d 
INNER JOIN Sales s ON d.AssociatedSalesDept = s.SalesDept
     WHERE d.RollUpTo = 0
  GROUP BY s.Date, d.LaborDeptId, d.RollUpTo;

Another option using a subquery. I like DavidN's better though.

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

I had to think about this one a bit, but here is one solution:

WITH cte AS (
SELECT CASE WHEN RollUpTo = 0 THEN LaborDeptId ELSE RollUpTo END AS LaborDeptId, AssociatedSalesDept
FROM departments)

SELECT s.date, d.LaborDeptid, SUM(s.Sales) AS TotalSales
FROM Sales s 
INNER JOIN cte d ON s.SalesDept = d.AssociatedSalesDept
GROUP BY s.date, d.LaborDeptid

Upvotes: 2

DavidN
DavidN

Reputation: 171

select s.[Date], coalesce(d2.LaborDeptid,d1.LaborDeptid) [LaborDept], sum(s.Sales) [TotalSales]
from Sales s
join Departments d1 on s.SalesDept = d1.AssociatedSalesDept
left join Departments d2 on d1.RollUpTo = d2.LaborDeptid
group by s.[Date], coalesce(d2.LaborDeptid,d1.LaborDeptid)

Upvotes: 2

Related Questions