SQL: IsNull, SUM over columns and COUNT

[Andreas]

Hi all,

I want to create the following query:

1 | 2 | Sum of 1+2
-----------------------------
2 3 5
1
5 0 5
3

I tried this:

SELECT isnull([num_c_1],0) + isnull([num_c_2],0) AS Expr1
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?

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

 

[Stefan Hoffmann]

hi Andreas,

Hi all,

I want to create the following query:

1 | 2 | Sum of 1+2
-----------------------------
2 3 5
1
5 0 5
3

SELECT
IIf(NOT IsNull([num_c_1]) AND NOT IsNull([num_c_2]),
[num_c_1]+[num_c_1],
NULL) AS [Sum of 1+2]
FROM tab_Cards_2008

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?

SELECT
Count(*)
FROM <QueryAbove>
WHERE [Sum of 1+2] = 5


mfG
--> stefan <--

 

[Andreas]

Hi Stefan,
your solution works great, thanks a lot ... only one issue left...

if I try this, it works (sum+count):
(SELECT
Count(*)
FROM (SELECT
IIf(NOT IsNull([num_c_1]) AND NOT IsNull([num_c_2]),
[num_c_1]+[num_c_2]+[num_c_3],
NULL) AS [enterprise_size]
FROM tab_Cards_2008)
WHERE [enterprise_size] = 0) AS enterprise_micro
FROM tab_Cards_2008

but, wenn I add the third column (I have to add in total 5) it returns
an error:

SELECT
Count(*)
FROM (SELECT
IIf(NOT IsNull([num_c_1]) AND NOT IsNull([num_c_2] AND NOT IsNull
([num_c_3]),
[num_c_1]+[num_c_2]+[num_c_3],
NULL) AS [enterprise_size]
FROM tab_Cards_2008)
WHERE [enterprise_size] = 0) AS enterprise_micro
FROM tab_Cards_2008

What is the problem?

Thank you very much!

Andreas

hi Andreas,

Hi all,

I want to create the following query:

1 |  2 | Sum of 1+2
-----------------------------
2    3         5
1
5    0         5
      3

SELECT
   IIf(NOT IsNull([num_c_1]) AND NOT IsNull([num_c_2]),
       [num_c_1]+[num_c_1],
       NULL) AS [Sum of 1+2]
FROM tab_Cards_2008

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?

SELECT
   Count(*)
FROM <QueryAbove>
WHERE [Sum of 1+2] = 5

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Andreas,

but, wenn I add the third column (I have to add in total 5) it returns
an error:

Run the summing query stand-alone. Save the query and use the saved
query for the counting.
This makes it easier to trap the error.

In the end, when everything runs fine, then you may create a single query.

mfG
--> stefan <--