October 4, 2011 at 12:24 pm
Any idea how to resolve this?
select count(distinct d.sn) as quantity, sum((l.labor) / 1000) as laborhour , sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37) as appLabor
from shippingLabor as l
join tbl_FinalInspAttr as g on l.rtid = g.fitem
join tbl_FinalGood as d on g.id = d.id
where d.dateInsp = '9/21/2011'
Thanks,
DJKhalif
October 4, 2011 at 12:44 pm
kabaari (10/4/2011)
Any idea how to resolve this?
select count(distinct d.sn) as quantity, sum((l.labor) / 1000) as laborhour , sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37) as appLabor
from shippingLabor as l
join tbl_FinalInspAttr as g on l.rtid = g.fitem
join tbl_FinalGood as d on g.id = d.id
where d.dateInsp = '9/21/2011'
Thanks,
DJKhalif
You problem is:
sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37)
You can't sum() with a count() inside the sum.
October 4, 2011 at 12:46 pm
Yes like mentioned you cannot perform multiple aggregations on a single column. You can however split this into two pieces in a subquery and perform your calculation outside. Like this.
select quantity, laborhour, Nominator/Denominator
from
(
select count(distinct d.sn) as quantity, sum((l.labor) / 1000) as laborhour ,
sum(cast(l.labor as decimal(18,2))) as Nominator, count(distinct d.sn) / 1000 * 23.37 as Denominator
from shippingLabor as l
join tbl_FinalInspAttr as g on l.rtid = g.fitem
join tbl_FinalGood as d on g.id = d.id
where d.dateInsp = '9/21/2011'
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2011 at 12:48 pm
Any idea how to resolve?
October 4, 2011 at 12:51 pm
Using the distributive properties of multiplication over addition, your formula
sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37) as appLabor
is equivalent to the following formula
sum(cast(l.labor as decimal(18,2))) * count(distinct d.sn) / 1000 * 23.37 as appLabor
Since the second formula moved the count outside of the sum, you no longer have problems with an aggregate inside an aggregate.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2011 at 1:00 pm
Threw an error near Line 12 ')'
October 4, 2011 at 1:02 pm
Drew,
This looks like it works. I'm tweaking it to get the correct results however, no longing throwing aggregate error.
Thanks,
DJ Khalif
October 4, 2011 at 1:14 pm
Sean Lange (10/4/2011)
Yes like mentioned you cannot perform multiple aggregations on a single column. You can however split this into two pieces in a subquery and perform your calculation outside. Like this.
There are a couple of problems with your code.
First, he is MULTIPLYING by the second value not dividing by it.
Second, the original poster converted his first value to decimal guaranteeing that the calculations used decimal arithmetic. When you split this up, you need to be careful. Since SQL seems to evaluate from left to right, your first calculation for your "denominator" uses INTEGER division, not decimal division, so that you get the wrong value when you finally use the decimal arithmetic based on the value 23.37.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2011 at 1:21 pm
drew.allen (10/4/2011)
Sean Lange (10/4/2011)
Yes like mentioned you cannot perform multiple aggregations on a single column. You can however split this into two pieces in a subquery and perform your calculation outside. Like this.There are a couple of problems with your code.
First, he is MULTIPLYING by the second value not dividing by it.
Second, the original poster converted his first value to decimal guaranteeing that the calculations used decimal arithmetic. When you split this up, you need to be careful. Since SQL seems to evaluate from left to right, your first calculation for your "denominator" uses INTEGER division, not decimal division, so that you get the wrong value when you finally use the decimal arithmetic based on the value 23.37.
Drew
Good points Drew. I would hope the OP would notice that it was now dividing instead of multiplying. 😀 The integer division was clearly an oversight on my part. Thanks for clarifying.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2011 at 2:48 pm
select count(distinct c.sn) as qty, sum(cast(l.labor as decimal(18,2))) / 1000 as labor, sum(cast(l.labor as decimal(18,2)))* count(distinct c.sn) / 1000 as laborhour, sum(cast(l.labor as decimal(18,2))) / 1000 * count(distinct c.sn) * 30.36 as appLabor
from chargeCheck as c
join production as p on c.workorder = p.workorder
join chargingLabor as l on p.item = l.rtid
where c.datein >= '9/19/2011' and c.datein <= '9/23/2011'
I ran it in this format, it works but I'm getting some incorrect values. I'm checking against Excel.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply