Link to home
Start Free TrialLog in
Avatar of innocent1973
innocent1973

asked on

URGENT- t-SQL SUM problem

Hello Experts
I have inserted a table structure, some data and my query as below.
But there is a problem on line:
SELECT  m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT

I want to replace the value as SUM(lbrutfiyat). Because this value is the sum of lbrufiyat columns for a specific lfisno.

Please help
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 mytest values('159 010 100','9999999',4,'HA',100,0)
GO 
UNION ALL
INSERT INTO mytest values('159 010 106','9999999',4,'HA',500,0)
GO 
UNION ALL
INSERT INTO mytest values('159 010 105','9999999',4,'HA',400,0)
GO 
UNION ALL
INSERT INTO mytest values('YYYYYYYY','9999999',2,'FA',5,833.33)
GO 
***************************************************


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'

Open in new window

Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

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'


Avatar of innocent1973
innocent1973

ASKER

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'

Open 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'

Open 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'

Open 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

Open 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

Open 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'


ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)

Open 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.