Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to sum only few values in a same column Expand / Collapse
Author
Message
Posted Saturday, February 2, 2013 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, Visits: 110
Hello all,
I am kind of new to sql server and I am stuck on this. It may sound stupid and simple but here is my situation:
CREATE TABLE For_Test(ID int not null,Name varchar(255),Amount decimal(18,2))
INSERT INTO For_Test VALUES (1,'A',220)
,(2,'B',340)
,(3,'C',540)
,(4,'D',780)

I just want to add amount of ID 1,2 and 3 excluding 4. There will be more than four rows in a table. So, how to do this?
Post #1414963
Posted Sunday, February 3, 2013 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 5,589, Visits: 24,946
Here is a quick method. NOT recommended for speed or flexibility ... If you would outline your requirements in greater detail perhaps some one can / will give you further assistance.

   CREATE TABLE For_Test(ID int not null,Name varchar(255),Amount decimal(18,2))
INSERT INTO For_Test VALUES (1,'A',220),(2,'B',340),(3,'C',540)
,(4,'D',780)

--I just want to add amount of ID 1,2 and 3 excluding 4.
SELECT SUM(Amount) FROM For_Test WHERE ID in (1,2,3)

--Different select statement but same result
SELECT SUM(Amount) FROM For_Test WHERE ID <> 4
--Yet a 3rd way
SELECT SUM(Amount) FROM For_Test WHERE ID < 4
--Yet a 4th way - using MOD function
SELECT SUM(Amount) FROM For_Test WHERE ID % 4 <> 0;
Result:
(No column name)
1100.00



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1415013
Posted Sunday, February 3, 2013 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, Visits: 110
Thank you very much. This will work for me now.
Post #1415019
Posted Sunday, February 3, 2013 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
park1432 (2/3/2013)
Thank you very much. This will work for me now.


Since you're new, I have to ask, which one is the "This" you speak of and why did you choose it?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415038
Posted Monday, February 4, 2013 2:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:54 PM
Points: 184, Visits: 1,111
What I would ask is "What characteristic/attribute of the fourth record makes you want to exclude it?"
Post #1415505
Posted Monday, February 4, 2013 6:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
@park1432,

Two way street here. We're actually interested in your problem and would like a bit more info if, for nothing else, a little education on a strange request.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse