SQL: IsNull, SUM over columns and COUNT

[Andreas]

Hi all,

I want to create the following query which calculates sums with NULL
values and COUNT the occurance of certain values:

1 | 2 | SumOf1_2
-----------------------------
2 3 5
1
5 0 5
3

I tried this:

SELECT isnull([1],0) + isnull([2],0) AS SumOf1_2
FROM tab_Cards_2008;

Unfortunately it doesnt work. Any ideas what my mistake is?

In a next step (or the same) I would like to COUNT eg. all the Sums of
5 (in this case 2 times) how is the best way to do that?

Maybe that:
SELECT COUNT (isnull([1],0) + isnull([2],0)) WHERE (isnull([1],0) +
isnull([2],0))=5 AS CountOf1_2
FROM tab_Cards_2008;

Sorry if this has a very easy answer, but I am new in SQL and have
tried several things to solve this.

Thanks for your help,

Andreas

 

[Duane Hookom]

IsNull() is not the same in Access and SQL Server.
I think you main issue might be that you are in a position of calculating
across fields rather than across records. This is generally a sign of an
un-normalized table structure.

However if you want to count null fields, you could try:
SELECT Abs(IsNull([Field1]) + IsNull([Field1])) As NumOfNull
FROM tab_Cards_2008;

I am totally confused by most of your question and really think you should
consider normalizing.

 

[Andreas]

Hi,

I solved the problem, thank you very much! And, I really think, the
database is normalized.

The solution:
SELECT
Count(*)
FROM (SELECT clng(nz([num_c_1],0)) + clng(nz([num_c_2],0)) + clng(nz
([num_c_3],0)) + clng(nz([num_c_4],0)) + clng(nz([num_c_5],0)) AS
SummeEmp FROM tab_Cards_2008)
WHERE [SummeEmp] = 0) AS enterprise_size_micro

Thanks

Andreas

IsNull() is not the same in Access and SQL Server.
I think you main issue might be that you are in a position of calculating
across fields rather than across records. This is generally a sign of an
un-normalized table structure.

However if you want to count null fields, you could try:
SELECT Abs(IsNull([Field1]) + IsNull([Field1])) As NumOfNull
FROM tab_Cards_2008;

I am totally confused by most of your question and really think you should
consider normalizing.
--
Duane Hookom
Microsoft Access MVP

Hi all,

I want to create the following query which calculates sums with NULL
values and COUNT the occurance of certain values:

1 |  2 | SumOf1_2
-----------------------------
2    3         5
1
5    0         5
      3

I tried this:

SELECT isnull([1],0) + isnull([2],0)  AS SumOf1_2
FROM tab_Cards_2008;

Unfortunately it doesnt work. Any ideas what my mistake is?

In a next step (or the same) I would like to COUNT eg. all the Sums of
5 (in this case 2 times) how is the best way to do that?

Maybe that:
SELECT COUNT (isnull([1],0) + isnull([2],0))  WHERE (isnull([1],0) +
isnull([2],0))=5 AS CountOf1_2
FROM tab_Cards_2008;

Sorry if this has a very easy answer, but I am new in SQL and have
tried several things to solve this.

Thanks for your help,

Andreas

 

[Duane Hookom]

From looking at the names of your fields and the expression you have to use
to total across fields, most veteran developers would guess your table is not
normalized.
--
Duane Hookom
Microsoft Access MVP

Hi,

I solved the problem, thank you very much! And, I really think, the
database is normalized.

The solution:
SELECT
Count(*)
FROM (SELECT clng(nz([num_c_1],0)) + clng(nz([num_c_2],0)) + clng(nz
([num_c_3],0)) + clng(nz([num_c_4],0)) + clng(nz([num_c_5],0)) AS
SummeEmp FROM tab_Cards_2008)
WHERE [SummeEmp] = 0) AS enterprise_size_micro

Thanks

Andreas

IsNull() is not the same in Access and SQL Server.
I think you main issue might be that you are in a position of calculating
across fields rather than across records. This is generally a sign of an
un-normalized table structure.

However if you want to count null fields, you could try:
SELECT Abs(IsNull([Field1]) + IsNull([Field1])) As NumOfNull
FROM tab_Cards_2008;

I am totally confused by most of your question and really think you should
consider normalizing.
--
Duane Hookom
Microsoft Access MVP

Hi all,

I want to create the following query which calculates sums with NULL
values and COUNT the occurance of certain values:

1 | 2 | SumOf1_2
-----------------------------
2 3 5
1
5 0 5
3

I tried this:

SELECT isnull([1],0) + isnull([2],0) AS SumOf1_2
FROM tab_Cards_2008;

Unfortunately it doesnt work. Any ideas what my mistake is?

In a next step (or the same) I would like to COUNT eg. all the Sums of
5 (in this case 2 times) how is the best way to do that?

Maybe that:
SELECT COUNT (isnull([1],0) + isnull([2],0)) WHERE (isnull([1],0) +
isnull([2],0))=5 AS CountOf1_2
FROM tab_Cards_2008;

Sorry if this has a very easy answer, but I am new in SQL and have
tried several things to solve this.

Thanks for your help,

Andreas