URGENT- t-SQL SUM problem

this dosen't make sense to me,
explain more
what is your expected results?

SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/1000 as result
from erdem_fistipleri2 as one join
(
    select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2  group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'

Thank you for your reply Reza,
I get the my expected result which I attached at my previously select statement. The problem is the value 1000 is not a dynamic sense. It should be the SUM of lbrutfiyat column

My desired output is like below:

smodel                                159 010 100      159 010 106                159 010 105
---------------------------------------------------------------------------------------------------------------                    
XXXXXXXX                  16.566998           66.267992      82.834990
YYYYYYYY                  82.833002           331.332008      414.165010

do you mean sum without where clause? if yes, do this:


SELECT  m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrufiyat) from erdem_fistipleri2) AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY
(
        SELECT  smodel,lbrutfiyat
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'

or you may would like to go for this query

SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/1000 as result
from erdem_fistipleri2 as one join
(
    select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA'   group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'

I have attached two screenshots. I am sure that you will exactly understand my problem from these screenshots.
Only thing that I did is I have changed the value 1000. Because this value should be the totals of lbrutfiyat for lfistipi='HA'
original.bmp
modified.bmp

OK
what about this now? :


SELECT  m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrufiyat) from erdem_fistipleri2 where sfistipi='HA') AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY
(
        SELECT  smodel,lbrutfiyat
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'

Perfect..
I have added an extra column named lfisno. Will this SUM value be valid for just this lfisno?

Resultset is:
999999999      XXXXXXXX                  159 010 100               16.667000
999999999      XXXXXXXX                  159 010 106               83.335000
999999999      XXXXXXXX                  159 010 105               66.668000
999999999      YYYYYYYY                  159 010 100               83.333000
999999999      YYYYYYYY                  159 010 106               416.665000
999999999      YYYYYYYY                  159 010 105               333.332000

SELECT  lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrutfiyat) from erdem_fistipleri2 where sfistipi='HA') AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY 
(
       SELECT  smodel,lbrutfiyat
       FROM    erdem_fistipleri2 c
       WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'

Select allOpen in new window

have you tried my both query? as TSQL with sub query and CROSS APPLY may slow down performance.

you can use RiteshShah query with these changes,
as he said , this has better performance,
sorry RiteshShah ,let me to deal with your query a bit :)

SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * c.lbrutfiyat/two.tot as result
from erdem_fistipleri2 as one join
(
   select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA'   group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'

and in this query two.tot can be used anywhere in your main query.

I get the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.lbrutfiyat" could not be bound.

that is alias Mismatch .. please see there is nothing called c used
I think its this table erdem_fistipleri2 c

Reza,
I think the following code is very closer to the solution. Only problem is the query should sum(lbrutfiyat) column for each lfisno values.
How can we modify the following part of the query:
select sum(lbrutfiyat) from erdem_fistipleri2 where sfistipi='HA'

SELECT  lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrutfiyat) from erdem_fistipleri2 where sfistipi='HA') AS RESULT 
INTO #subresults 
FROM    erdem_fistipleri2 m 
CROSS APPLY  
( 
       SELECT  smodel,lbrutfiyat 
       FROM    erdem_fistipleri2 c 
       WHERE sfistipi='HA' 
)c 
WHERE m.sfistipi='FA'

Select allOpen in new window

>>orry RiteshShah ,let me to deal with your query a bit :)<<

no issue Reza, you can not DEAL A BIT but you can have complete deal with it :) basically as and when possible, I try to avoid sub query in the sense you have used it. it really kills performance especially if table is big and index is not maintained properly. hope you can understand the point I want to convey.

Thank you very much for your great comment :))))))

Could you please help me about the value 1000. Because my only purpose is replace this static value to dynamic column values.
And other issue is the query should sum the lbrutfiyat values for each lfisno values.
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/1000 as result
from erdem_fistipleri2 as one join
(
select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA' group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'

look at attached code:

total is already comming from join query and whatever value you will assign in @val, will be in calculation. do you need this thing only?

declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA' group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'

Select allOpen in new window

No.
When I apply the following code then my resultset is as follows:

XXXXXXXX                  159 010 100               16.667000
XXXXXXXX                  159 010 106               83.335000
XXXXXXXX                  159 010 105               66.668000
YYYYYYYY                  159 010 100               83.333000
YYYYYYYY                  159 010 106               416.665000
YYYYYYYY                  159 010 105               333.332000


For the first data row SUM of HA values is 1000. (100+500+400)

'159 010 100' ---> 166.67 x (100/1000) = 16.667

'159 010 106'----> 166.68 x (500/1000) = 83.335

'159 010 105'-----> 166.68 x (400/1000) = 66.668

I should put something instead of value 1000 in the query

-- step 1: build a intermediate table to get the data in a more relational format 
SELECT  m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY 
(
        SELECT  smodel,lbrutfiyat
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'

SELECT  *
FROM    #subresults

Select allOpen in new window

SELECT  m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/s.total AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY
(
        SELECT  smodel,lbrutfiyat
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)c
cross apply
(
SELECT  sum(lbrutfiyat) as total
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)s
WHERE m.sfistipi='FA'

you are getting 16.667, 83.335 etc. because you are not making SUM of lbrutfiyat, see below given two query. one will show your the value you wanted to see but without SUM and second one calculating value with SUM.

--method 1
declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,lbrutfiyat as tot from erdem_fistipleri2 where sfistipi='HA' --group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'
GO

--method2
declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA' group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'
GO

Select allOpen in new window

I think that you can remove part of your query as below:
try this one now:

SELECT  m.smodel,m.smodel AS col,m.lIlaveMaliyetTutar * m.lbrutfiyat/s.total AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
cross apply
(
SELECT  sum(lbrutfiyat) as total
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)s
WHERE m.sfistipi='FA'

Ritesh..Thank you very much, because you waste many times for me.

I have attached the complete code.

This query gives correct resultset for this data. As you noted, there is just one lfisno=9999999.
If any other lfisno be added then the value 1000 will not be valid.
So, I have to replace this value as a sum of lbrutfiyat.

I am really sorry for misunderstanding but the case is really difficult.

 

create table erdem_fistipleri2 
( 
smodel varchar(50), 
lfisno varchar(50), 
nstoktipi int, 
sfistipi char(50), 
lbrutfiyat float, 
lIlaveMaliyetTutar float 
) 
****************************************************** 
 
 
INSERT INTO erdem_fistipleri2 values('XXXXXXXX','9999999',2,'FA',1,166.67) 
GO  
UNION ALL 
INSERT INTO erdem_fistipleri2 values('159 010 100','9999999',4,'HA',100,0) 
GO  
UNION ALL 
INSERT INTO erdem_fistipleri2 values('159 010 106','9999999',4,'HA',500,0) 
GO  
UNION ALL 
INSERT INTO erdem_fistipleri2 values('159 010 105','9999999',4,'HA',400,0) 
GO  
UNION ALL 
INSERT INTO erdem_fistipleri2 values('YYYYYYYY','9999999',2,'FA',5,833.33) 
GO 

-- step 1: build a intermediate table to get the data in a more relational format 
SELECT  m.lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT
INTO #subresults
FROM    erdem_fistipleri2 m
CROSS APPLY 
(
        SELECT  smodel,lbrutfiyat
        FROM    erdem_fistipleri2 c
        WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'

SELECT  *
FROM    #subresults

-- build the dynamic cross tab query
DECLARE 
        @SQL1 NVARCHAR(4000),        
        @SQL2 NVARCHAR(4000),        
        @SQL3 NVARCHAR(4000)

SELECT @SQL1 = 'SELECT  smodel'+CHAR(10)
--===== Create the "Mostly Static" section of the code 
SELECT @SQL3 = ' FROM #subresults group by smodel'
--===== The "Hard" Part - Concatenation to Form the Date Columns 
SELECT @SQL2 = COALESCE(@SQL2,'')
              + '       ,SUM(CASE WHEN col = ' + QUOTENAME(s.col,'''')
              + ' THEN result ELSE 0 END) AS [' + s.col + ']' + CHAR(10)   
              FROM #subresults   s 
              GROUP BY col

--===== Print the Dynamic SQL (uncomment for troubleshooting
--  PRINT @SQL1 + @SQL2 + @SQL3
--===== Execute the Dynamic SQL to create the desired report   
EXEC (@SQL1 + @SQL2 + @SQL3)

Select allOpen in new window

you will not be able to use #subresults temp table in your @SQL2 as it will go out of socpe. you are creating local temp table which is accessible from the same scope not from other scope.