# Summing Line Items In SQL

@SQLZim helped me on my ? earlier and the syntax works for about 90% of my cases, but the 10%, I can not say the same for. Let's use sample DDL below, and the query produces accurately for `emp1` but not for `emp2`

My Expected output would be `emp1 = .4050` and `emp2 = .2761` however the query results produce `emp1 = .4050` BUT `emp2 = .6276`

Why the discrepancy in calculations? For further breakdown, this is the calculation I am trying to achieve

``````emp1 = (1111-(661+(0*.75)))/1111 = .4050
emp2 = (1954.85-(1120.65+(392.7*.75)))/1954.85 = .2761
``````

Which should lead to the results above, however if you look at my DDL below I do not get that outcome, I get

``````employee    AnnualSales DeptSales   Tax     SomeCalc
emp1        1111.00     661.00      0.00    0.405040
emp2        1954.85     1120.65     392.70  0.627618
``````

DDL

``````Declare @Calc Table
(
employee varchar(100)
,annualsales decimal(10,2)
,dep1sales decimal(10,2)
,dep2sales decimal(10,2)
,dep3sales decimal(10,2)
,dep4sales decimal(10,2)
,dep5sales decimal(10,2)
,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', '1954.85', NULL, '432.45', NULL, NULL, NULL, NULL)
,('emp2', NULL, NULL, '688.20', NULL, NULL, NULL, '392.70')

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales    =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
-sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
+sum(isnull(tax,0))
)
/nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc
``````

How should my query be altered in order for the above expected output to be produced?

The issue with the old query is that it wasn't calculating tax *.75 appropriately.

rextester: http://rextester.com/ZGQ51639

``````Declare @Calc Table
(
employee varchar(100)
,annualsales decimal(10,2)
,dep1sales decimal(10,2)
,dep2sales decimal(10,2)
,dep3sales decimal(10,2)
,dep4sales decimal(10,2)
,dep5sales decimal(10,2)
,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
-(
sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
+(sum(isnull(tax,0))*.75)
)
)
/nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc
``````
• Happy to Help, Michael! – SqlZim Dec 16 '16 at 20:06