1 or 2 passes - 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

SQL Server 2000 Forums

SQL Server Development (2000)

1 or 2 passes

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-17 : 21:06:17

Hello, will the following query make 1 or 2 passes in the table
"Credit"? Does anyone know how SQL server would handle this?
thanks.

SELECT SUM(Amount) as a1 FROM Credit where MethodOfPayment = '1'
Union
SELECT SUM(Amount) as a1 FROM Credit where MethodOfPayment = '2'

If it does both select statements separately, would I be better

doing

SELECT * From credit WHERE MethodOfPayment Between 1 and 2

and then extracting the info using if statements in my code?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-17 : 21:09:16

How about


SELECT (SELECT SUM(Amount) as a1 FROM Credit where MethodOfPayment = '1') As Sum_Of_Method1, 
(SELECT SUM(Amount) as a1 FROM Credit where MethodOfPayment = '2') AS Sum_Of_Method2

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-17 : 21:18:09

Hi, I was doing some research and I came up with

SELECT ISNULL(SUM(CASE WHEN MethodOfPayment = 1 THEN Amount Else NULL END),0) as a,
ISNULL(SUM(CASE WHEN MethodOfPayment = 2 THEN Amount Else NULL END),0) as b
From Credit

Which is better, my way or yours. It seems with this method, you are
only doing 1 pass.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-17 : 22:16:05

I'm not sure which is better. Drop them both into QA and look at the execution plans.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 04:23:17

maybe i'm nuts but wouldn't this do the same thing??

SELECT MethodOfPayment, SUM(Amount) as a1
FROM Credit
where MethodOfPayment in (1,2)
group by MethodOfPayment
order by MethodOfPayment

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-18 : 06:28:39

I'm all with spirit1 here, and think everyone else is nuts ;-)

And if You want 2 column, 1 row resultset you would do:
SELECT
SUM(CASE MethodOfPayment WHEN 1 THEN Amount ELSE 0 END) AS a1,
SUM(CASE MethodOfPayment WHEN 2 THEN Amount ELSE 0 END) AS a2
FROM Credit WHERE MethodOfPayment in (1,2)

You must unlearn what You have learnt
/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources