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.
| Author |
Topic |
|
cplusplus
Aged Yak Warrior
567 Posts |
 Posted - 2013-08-06 : 12:36:31
|
| How to chk it null values and also keep decimals. all columns has decimals, but when i am using my tool i am getting whole numbers ignoring decimals on my front end tool, how can i query and show decimals also.If any column tot_rev=0 or Act_count =0 then Avg_chg should be zero else need to calculate.Select (tot_rev/Act_count) as Avg_CHG from tbl_revenues;Thanks a lot for the helpful info. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-06 : 13:11:34
|
| Try this:[CODE]Select COALESCE(CAST(tot_rev as NUMERIC(10, 2))/NULLIF(Act_count, 0), 0) as Avg_CHG from tbl_revenues;[/CODE]Make sure your front end tool has appropriate formatting set on the associated display field. |
 |
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-08-06 : 13:26:41
|
| I am planning to use within an etl tool the query straight forward, is there a way to have a query logic without coalesce. |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-06 : 13:30:26
|
If you don't use COALESCE() you get NULL when the denominator is zero.quote:
Originally posted by cplusplus
I am planning to use within an etl tool the query straight forward, is there a way to have a query logic without coalesce.
|
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-06 : 13:34:46
|
| Which ETL tool are you using,you can use IIF() or CASE() instead |
|
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-08-06 : 13:37:30
|
| Yes it is possible with case, can you pls show how to handle using case within query. |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-06 : 14:22:04
|
| [CODE]Select CASE WHEN (CAST(tot_rev as NUMERIC(10, 2))/NULLIF(Act_count, 0)) IS NULL THEN 0 ELSE (CAST(tot_rev as NUMERIC(10, 2))/NULLIF(Act_count, 0)) END as Avg_CHG from tbl_revenues;[/CODE] |
|
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 10:36:56
|
| Select (ISNULL(CAST(tot_rev AS DECIMAL(18,0)),0)/ISNULL(CAST(Act_count AS NUMERIC(18,0),0)) as Avg_CHG from tbl_revenuesP.Siva |
|
|
|
|
|
|
|
|
select query aggregate function dividing