SQL Server Performance Forum – Threads Archive

Is such a query possible ?

Hi Guys
I have a database table which has values in the format
ID Val Type Effect
1 10 A 12.5
2 11 A 20
3 12 A 30
4 12 B 40
The ID is the primary key and is unique. Now the problem is, i need to calculate the Sum of effect(column).
But since the Val(column) has 2 rows with value 12, i should be taking ‘Type B’ value instead of Type A value for calculating the sum. Meaning if type B exists for a Val, then i have to take the effect value of that particular row to get the output for sum function. I hope my question is clear.
The final value for Sum(Effect) should be 72.5 (12.5 + 20 + 40). How do i write a query to make this possible. I have tried Distinct, Group BY…. but nothing works. Please help Regards..

select 1 [ID], 10 Val, ‘A’ Type, 12.5 Effect
into #t
union select 2, 11, ‘A’, 20
union select 3, 12, ‘A’, 30
union select 4, 12, ‘B’, 40 SELECT SUM(Effect)
from #t join
(SELECT Val, max(Type) Type
from #t
group by Val) as G on #t.Val = G.Val and #t.Type = G.Type Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

declare @val as int
declare @effect as money
declare @sum_effect as money
declare cur_cnt cursor for
select val from table_name group by val
open cur_cnt
fetch next from cur_cnt into @val
set @sum_effect=0
while @@fetch_status=0
begin
select top 1 @effect=effect from table_name where [email protected] order by type desc
select @[email protected][email protected]
fetch next from cur_cnt into @val
end
select @sum_effect
close cur_cnt
deallocate cur_cnt Try this…..

Hi Aviel,
Thanks for the reply. When i tried doing that i got 2 errors
Incorrect syntax near the keyword ‘join’.
Incorrect syntax near the keyword ‘as’. But when i execute this statement
SELECT Val, max(Type) Type
from #t
group by Val there is no error and it shows the correct output. So i guess it should be with the join syntax. regards
abhi

Which version of SQL Server are you working with? Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Hi Aviel,
Im working with SQL Server 2000. Just now i was trying to run the queries in query analyser, but after running the same in sql stored procedure, it works. Thank you very much for ur fast solution. Three Cheers….
and thanks to ranjith for providing another option also…. Abhi

It should work also in QA. Make sure you select all the query text. I tested that in QA. Any way, you are fine now. Cheers.
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Here is another solution: select sum(tablename.effect)
from tablename t
join (select val, max(type) type
from tablename
group by val) v
on t.val = v.val
and t.type = v.type

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by chrishatcher Here is another solution: select sum(tablename.effect) from tablename t join (select val, max(type) type from tablename group by val) v on t.val = v.val and t.type = v.type <hr height="1" noshade id="quote"></blockquote id="quote"> Hi Chris, I liked the option which u gave.[^] i think its the best of all for this Quest. Ofcourse it’s a small problem but after comparing three solutions,the one aviel gave using temp. table, and the one i gave which uses round trips. I found ur best as it avoids round trips and server resources too for creating and inserting records into temp. table. So every problem needs deep thinking and fully analysing the problem. Even i didnt found my solution good at first glance when i posted but then i thought its a solution but was not good.[V] I’ll try to give best possible solution if i can as just the solution is not everything, it has to be best.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

The temp table was just to put the data in a table that the query can run on some data. instead you can run it on your original table.
The solution is a LOGICAL solution. You can run the query on every table. Cheers. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

]]>