Calculations in Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculations in Select Query

Author

Topic

sreeraj216
Starting Member

4 Posts

Posted - 2008-12-08 : 10:31:31

Hi SQL gurus,
I am trying to do calculations in select statement.
I have columns:
Table1.Fyear,
Table2.Cost; data is ('Initial Cost','Final Cost','Cost Inc','Cost dec')
Table2.Amt

And I want to calculate Net Cost = 'Final Cost'-'Initial Cost' and
Cost Bal = 'Cost Inc'-'Cost dec'

I tried to do like this, but didn't get success.

select sum(Amt),
'Final Cost'-'Initial Cost' as Net Cost,
'Cost Inc'-'Cost dec' as Cost Bal
from (
select distinct
table1.fyear
sum(table2.Amt)
from table1, table2
where
table1.projnum=table2.projectnum and
table1.fyear in (2008,2009) and
table2.cost in ('Initial Cost','Final Cost','Cost Inc','Cost dec')
group by
table1.fyear
)

Can anyone please help me on this.

Thanks in Advance
Madhuri

daniel7912
Starting Member

7 Posts

Posted - 2008-12-08 : 10:46:59

Try this, I have tried it and it works perfectly.

select sum(Amt),
'Final Cost'-'Initial Cost' as Net Cost,
'Cost Inc'-'Cost dec' as Cost Bal
from (
select distinct
table1.fyear
sum(table2(you're gay).Amt)
from table1, table2
where
table1.punjab_boobies=table2.projectnum and
table1.fyear in (2008,2009) and
table2.cost in ('Initial Cost','Final Cost','Cost Inc','Cost dec')
group_sex by
table1.flippingyear
As table1.table_.table
FOR
table.table_.table
DO
table2.table_.table
WHILE
table3.table_.table

IF
table4.table_.table = table1, 2 AND 3 AND THEN 5

{
system("PAUSE");
EXIT(1);
}

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 02:54:49

is this ms sql server?

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-09 : 05:08:33

Is the structure of your table like this?

Table 1
ProjNum | Fyear
1 2008
2 2009
3 2008

Table 2
ProjNum | Cost | AMT
1 Initial Cost 10.00
1 Final Cost 11.00
1 Cost Inc 10.00
1 Cost Dec 11.00
2 Initial Cost 1.00
2 Final Cost 2.00
2 Cost Inc 1.00
2 Cost Dec 2.00
3 Initial Cost 11.00
3 Final Cost 21.00
3 Cost Inc 11.00
3 Cost Dec 11.00

if so...you can try this...

select sum(AMT) AMT,
ProjNum,COST
into #Temp_Table2_InitialCost_Summary
from Table_2
where COST = 'Initial Cost'
group by ProjNum,COST
--Group by Cost Type

select sum(AMT) AMT,
ProjNum,COST
into #Temp_Table2_FinalCost_Summary
from Table_2
where COST = 'Final Cost'
group by ProjNum,COST
--Group by Cost Type

select sum(AMT) AMT,
ProjNum,COST
into #Temp_Table2_CostInc_Summary
from Table_2
where COST = 'Cost Inc'
group by ProjNum,COST
--Group by Cost Type

select sum(AMT) AMT,
ProjNum,COST
into #Temp_Table2_CostDec_Summary
from Table_2
where COST = 'Cost Dec'
group by ProjNum,COST
--Group by Cost Type

SELECT sum(AMT) AMT,
(sum(c.AMT) - sum(d.AMT)) as [Net Cost],
(sum(b.AMT) - sum(a.AMT)) as [Cost Balance],
e.fyear
FROM #Temp_Table2_CostDec_Summary a
join #Temp_Table2_CostInc_Summary b
on(a.ProjNum = b.ProjNum)
join #Temp_Table2_FinalCost_Summary c
on(b.ProjNum = c.ProjNum)
join #Temp_Table2_InitialCost_Summary d
on(c.ProjNum = d.ProjNum)
join Table_1 e
on(d.ProjNum = e.ProjNum)
GROUP BY e.fyear
--Generate the final output.

There can be more approaches better than this...
HTH. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources