Total columns by row - 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

General SQL Server Forums

New to SQL Server Programming

Total columns by row

Author

Topic

cbjones
Starting Member

4 Posts

Posted - 2011-07-12 : 19:37:59

First, I am a new query writer. I am learning from books and trial and error, and get mostly error but improving...no formal training so pardon me if I ask stupid questions.

The data in BUDGET is stored as a “money” data type. I intend to convert them to a round number. But:

I want to total “RentBucks” + “FoodBucks” + “AutoBucks” + “SaveBucks” + “Odd1Bucks” and call the total “BudgetBucks”. I want to show NULL values, if any, as zero in the BudgetBucks column.

So far, I show the columns with un-totaled integers. I have failed to add them successfully in the BudgetBucks column. I have left my attempts to add them out of the query since they all failed. Can anyone help me?

Please be easy on me, I already feel stupid...thanks!

MSSQL QUERY (simplified example):

SELECT

ISNULL(CAST(MoneyFile.Rent AS INT(10)),0) RentBucks,
ISNULL(CAST(MoneyFile.Food AS INT(10)),0) FoodBucks,
ISNULL(CAST(MoneyFile.Auto AS INT(10)),0) AutoBucks,
ISNULL(CAST(MoneyFile.Save AS INT(10)),0) SaveBucks,
ISNULL(CAST(MoneyFile.Odd1 AS INT(10)),0) Odd1Bucks

FROM BUDGET

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-12 : 21:06:30

INT in SQL server doesn't have precision. So the (10) needs to go away. You can also remove the prefix, and of course, there's no FROM clause.

SELECT

ISNULL(CAST(Rent AS INT), 0) RentBucks,
ISNULL(CAST(Food AS INT), 0) FoodBucks,
ISNULL(CAST([Auto] AS INT), 0) AutoBucks,
ISNULL(CAST([Save] AS INT), 0) SaveBucks,
ISNULL(CAST(Odd1 AS INT), 0) Odd1Bucks

FROM MoneyFile

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-12 : 21:08:28

This may be what you're after though.

SELECT

ISNULL(CAST(Rent AS INT), 0) +
ISNULL(CAST(Food AS INT), 0) +
ISNULL(CAST([Auto] AS INT), 0) +
ISNULL(CAST([Save] AS INT), 0) +
ISNULL(CAST(Odd1 AS INT), 0)

as BudgetBucks

FROM MoneyFile

cbjones
Starting Member

4 Posts

Posted - 2011-07-13 : 06:54:06

russell,

You have just solved my problem! That is exactly the result I was looking for and, for lack of full command of the language, could not solve myself...but I am learning! Thank you, thank you, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources