Summary column not working - 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

Summary column not working

Author

Topic

pinghanling
Starting Member

2 Posts

Posted - 2014-07-04 : 06:33:06

Hi,

I have a table census data containing the number of people having ages ranging from 0 to 120 years. Year 0 is in a column called F_0 and year 120 is in a column called F_120 with everything in between.

For example, when I create a script for the table the column for year 0 appears as: [F_0] [numeric](38, 8) NULL

I've already created 2 summary columns (people 0 to 18 years old and people 19 to 59 years old).

I follow the same methodology to create a summary table for people from 60 to 100 years old, but all the calculated values are NULL.

So the following is successful:
[Under_19] AS (((((((((((((((((([F_0]+[F_1])+[F_2])+[F_3])+[F_4])+[F_5])+[F_6])+[F_7])+[F_8])+[F_9])+[F_10])+[F_11])+[F_12])+[F_13])+[F_14])+[F_15])+[F_16])+[F_17])+[F_18]) PERSISTED,

But this is not:
[Over_60] AS (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((([F_60]+[F_61])+[F_62])+[F_63])+[F_64])+[F_65])+[F_66])+[F_67])+[F_68])+[F_69])+[F_70])+[F_71])+[F_72])+[F_73])+[F_74])+[F_75])+[F_76])+[F_77])+[F_78])+[F_79])+[F_80])+[F_81])+[F_82])+[F_83])+[F_84])+[F_85])+[F_86])+[F_87])+[F_88])+[F_89])+[F_90])+[F_91])+[F_92])+[F_93])+[F_94])+[F_95])+[F_96])+[F_97])+[F_98])+[F_99])+[F_100])+[F_101])+[F_102])+[F_103])+[F_104])+[F_105])+[F_106])+[F_107])+[F_108])+[F_109])+[F_110])+[F_111])+[F_112])+[F_113])+[F_114])+[F_115])+[F_116])+[F_117])+[F_118])+[F_119])+[F_120]) PERSISTED,

Can someone perhaps tell me why?

Thanks
Hanlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-04 : 06:40:21

Some value has NULL.

[Over_60] AS (ISNULL(F_60, 0) + ISNULL(F_61, 0) + ISNULL(F_62, 0) + ... )

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pinghanling
Starting Member

2 Posts

Posted - 2014-07-04 : 08:30:39

I think I know why this happens.

It's actually an ArcGIS table that has <Null> in some of the columns for older people and that messes up the calculation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources