Jon Galloway

To save some time, post your question on SQLTeam.com. You should get an answer quickly if you've provided enough information. Don't forget to subscribe to the thread so that you know when an answer has been posted.

Yep. Did a brief search there, but found it in a google group posting first. I found the answer in about 10 minutes, so I didn't post.

http://www.sqlteam.com is great - I've spent hours there over the years.

http://www.sqlservercentral.com/ and http://www.sql-server-performance.com/ are also pretty good resources.

Hi,

Had very similar problem - in my case it was sum, instead of avg and your solution obviously worked here as well, tx a lot!

I had been trying to get the average of two summed columns (weighted average) and this was a great solution.

Thanks!

-Mike

Thanks a bunch mate, I wasted loads of time trying to figure this one out.

Worked here too - good work fella!

I am using a reporting software called Showcase Strategy and am ecountering the same message.  The software uses a different type of select statement.

I am trying to get an Average for the statement below, any ideas on how I can avoid the error?

CAST( TIMESTAMPDIFF( SQL_TSI_DAY, MIN( event_dt ), MAX( work_dt ) ) AS real )

Just great, where can I dig it? =) Serious this saved me a lot of time and it was the first hit on google.

Thanks

great! been puzzled for a while, thank you!

Thank you very much, you saved me!

This helped me really... Thanks..

Hey thanks a lot for this tip....saved my neck :)

ver y thanks  great!

Great job, I had a similar problum (with SUM) and this got it working great!!!

Great! I had similar problem, I was using Sum on calculated weight and volume, selecting from other subquery , Used this Worked Great for me.

-Srini

"Usually the simplest solution is the most elegant"

(...with apologies to Occam)

Thanks!

Great .......thnx a ton

select sum(datediff(s,att_date,(select in_time from sys_para where tran_date between '2009/04/01' and '2010/03/31'))),out_time from attendance

I've been using this technique for awhile but went looking for another way about doing this in SQL. See code below. Is there another way someone has devised that will give the same result? This is a small part of a larger query and it does not like it when I use the nested query. Any thoughts would be appreciated. This query only returns one value.

Query:

SELECT SUM(TEST) as SamplePct

FROM(

SELECT LB.Pct*(((LB.AmtBud /(

(SELECT CASE WHEN PR.ProjectType='FP'

THEN ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)

WHEN PR.ProjectType='TM'

THEN ISNULL(SUM(ISNULL(LB.BillBud,0.00)),0.00)

ELSE ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)

END

FROM LB WHERE LB.WBS1=PR.WBS1)

+

(SELECT CASE WHEN PR.ProjectType='FP'

THEN ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)

WHEN PR.ProjectType='TM' THEN ISNULL(SUM(ISNULL(EB.BillBud,0.00)),0.00)

ELSE ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)

END

FROM EB

left outer join PR as PR1 on EB.WBS1=PR1.WBS1 AND EB.WBS2=PR1.WBS2 AND EB.WBS3=PR1.WBS3

WHERE EB.WBS1=PR.WBS1 AND EB.Account!='4020.00')))*100)/100)

as TEST

FROM LB

left outer join PR on LB.WBS1=PR.WBS1 AND LB.WBS2=PR.WBS2 AND LB.WBS3=PR.WBS3

WHERE PR.WBS1='05012-11'

UNION ALL

SELECT EB.Pct*(((

EB.AmtBud /((SELECT CASE WHEN PR.ProjectType='FP'

THEN ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)

WHEN PR.ProjectType='TM'

THEN ISNULL(SUM(ISNULL(LB.BillBud,0.00)),0.00)

ELSE ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)

END

FROM LB WHERE LB.WBS1=PR.WBS1)

+ (SELECT CASE WHEN PR.ProjectType='FP'

THEN ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)

WHEN PR.ProjectType='TM' THEN ISNULL(SUM(ISNULL(EB.BillBud,0.00)),0.00)

ELSE ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)

END

FROM EB

left outer join PR as PR1 on EB.WBS1=PR1.WBS1 AND EB.WBS2=PR1.WBS2 AND EB.WBS3=PR1.WBS3

WHERE EB.WBS1=PR.WBS1 AND EB.Account!='4020.00')))*100)/100)

as TEST

FROM EB

left outer join PR on EB.WBS1=PR.WBS1 AND EB.WBS2=PR.WBS2 AND EB.WBS3=PR.WBS3

WHERE PR.WBS1='05012-11'

)

as TESTTABLE

Thanks in advance ~CW

Helped me a lot!!!

Great work.Thank You.

Thanks, this was really helpful.

Select CashAmount=Sum(Case When P.PayModeCode=(select paymodecash from pos_mastercode) THEN P.Amount END)

pls help

This is just some random cooment that i wanted to write to see if i could blast the the hell out of this site later. If it sticks il will come back around and stuff the hell out of it with some links. Please wait for my return and i will take advantage of this opening.