sum(),Cummulative addition,Case statement

Question

Post reply

sum(),Cummulative addition,Case statement

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

suppose if i have like

insert into #test

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

union all

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

union all

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

union all

select 0

union all

select 1

my requirement is ,simply i want to add the above values.

So the first two ways are working fine. But the #3 is just giving the max value. so we can't consider it as the solution for this kind of problem.

Obviously it should display only the max value only.

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

karthikeyan (5/19/2008)
Once I repair the error in the #2 code you provided, it gives me the following...
-----------------------------------------------------
3.0
(1 row(s) affected)
-----------
3
(1 row(s) affected)
-----------
1
(1 row(s) affected)
What's not expected in that?
I want the cummulative added value ( i.e 3). But #3 just displaying the max(i.e 1) value.

So.... what's unexpected about that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 3

what i am saying is #3 is not suitable for our requirement. #1 and #2 is working fine.

karthik

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

All 3 work just fine if all you want to do is to detect that there is one or more. #3 is the fastest for this purpose. #1 is the second fastest (so far, haven't tried COUNT).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 5

All 3 work just fine if all you want to do is to detect that there is one or more. #3 is the fastest for this purpose. #1 is the second fastest (so far, haven't tried COUNT).

i am very much confused.

I have a table called #emp.

create table #emp

(

Sample Bit

)

Insert into #emp

select 1

union all

select 1

union all

select 1

select 0

I want to add the above BIT values.

As aggregate functions are not working directly with BIT data type ,

i have executed the below statement to get the cummulative added value (i.e 3) do you agree till here ?

simply i want to SUM the above sample column.

ie 1+1+1+0 = 3

Do you agree till here ?

Ok. Now....

select sum(convert(int,Sample)) from #emp

I got 3.

Again,

select sum(sign(Sample)) from #emp

my result is 3. OK.

Two ways are working fine.

Now..........

select MAX(cast (sample as Int)) from #emp

will it give '3' or '1' ? obviously 1.yes. bcoz i got 1, yes absolutely it is doing its job.Max gave the maximum value.

Please clarify my doubt.

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

karthikeyan (5/20/2008)
All 3 work just fine if all you want to do is to detect that there is one or more. #3 is the fastest for this purpose. #1 is the second fastest (so far, haven't tried COUNT).
i am very much confused.
I have a table called #emp.
create table #emp
(
Sample Bit
)
Insert into #emp
select 1
union all
select 1
union all
select 1
select 0
I want to add the above BIT values.
As aggregate functions are not working directly with BIT data type ,
i have executed the below statement to get the cummulative added value (i.e 3) do you agree till here ?
simply i want to SUM the above sample column.
ie 1+1+1+0 = 3
Do you agree till here ?
Ok. Now....
select sum(convert(int,Sample)) from #emp
I got 3.
Again,
select sum(sign(Sample)) from #emp
my result is 3. OK.
Two ways are working fine.
Now..........
select MAX(cast (sample as Int)) from #emp
will it give '3' or '1' ? obviously 1.yes. bcoz i got 1, yes absolutely it is doing its job.Max gave the maximum value.
Please clarify my doubt.

Like Jeff mentioned earlier - your requirement needed to know if there was at least one restriction in the given group. All three calculations will return 0 if there are NO restrictions in the group, and something other than 0 if there are restrictions. The MAX doesn't tell you HOW MANY there are, just tells you that there are at least one.

Again - per your own requirements, your logic requires knowing that there is at least one, but you don't do anything special depending on how many there might be, so the COUNT is irrelevant per se. The only important part is that it's different from 0.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

Correct...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 8

karthik M

One Orange Chip

Points: 29139

May 21, 2008 at 12:15 am

#818366

Oh...Correct.

Thank a lot teachers.:)

karthik