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

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
RiteshShah

8/22/2022 - Mon
Reza Rad

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

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'


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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Reza Rad

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'


RiteshShah

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Reza Rad

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'


innocent1973

ASKER
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

RiteshShah

have you tried my both query? as TSQL with sub query and CROSS APPLY may slow down performance.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Reza Rad

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.


innocent1973

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

that is alias Mismatch .. please see there is nothing called c used
I think its this table erdem_fistipleri2 c
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
innocent1973

ASKER
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

RiteshShah

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

ASKER
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'
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
RiteshShah

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

innocent1973

ASKER
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

Reza Rad

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'


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RiteshShah

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

Reza Rad

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
RiteshShah

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
innocent1973

ASKER
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
RiteshShah

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.