Geek Answers Handbook

Count the difference, and null is excluded

I am using SQL Server 2005. With the query below (simplified from my real query):

select a,count(distinct b),sum(a) from 
(select 1 a,1 b union all
select 2,2 union all
select 2,null union all
select 3,3 union all
select 3,null union all
select 3,null) a
group by a

Is there any way to make the counter without getting

"Warning: Null is excluded by aggregation or other SET operation."

Here are the alternatives I can think of:

  • Disabling ANSI_WARNINGS

  • The division into two queries: one with a counter and a where clause to exclude zeros, one with a sum:

    select t1.a, t1.countdistinctb, t2.suma from
(
    select a,count(distinct b) countdistinctb from 
    (
        select 1 a,1 b union all
        select 2,2 union all
        select 2,null union all
        select 3,3 union all
        select 3,null union all
        select 3,null
    ) a
    where a.b is not null
    group by a
) t1
left join
(
    select a,sum(a) suma from 
    (
        select 1 a,1 b union all
        select 2,2 union all
        select 2,null union all
        select 3,3 union all
        select 3,null union all
        select 3,null
    ) a
    group by a
) t2 on t1.a=t2.a

  • Ignore warning on client

Is there a better way to do this? I will probably go along route 2, but I don't like code duplication.

+5
source share
4 answers
select a,count(distinct isnull(b,-1))-sum(distinct case when b is null then 1 else 0 end),sum(a) from 
    (select 1 a,1 b union all
    select 2,2 union all
    select 2,null union all
    select 3,3 union all
    select 3,null union all
    select 3,null) a
    group by a

Eoin . , , - , , .

+5

, , ,

CASE WHEN Column IS NULL THEN -1 ELSE Column END AS Column

-1 , / , ...

SELECT  
    CASE WHEN t1.a = -1 THEN NULL ELSE t1.a END as a
    , t1.countdistinctb
    , t2.suma
+2

, , Google, .

NULL - (tm).

COUNT() , DISTINCT.

DISTINCT .

:

WITH A(A) AS (SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT 1)
SELECT COUNT(*) FROM (SELECT DISTINCT A FROM A) B;

This allows a use COUNT(*)that does not ignore NULL (since it takes into account records, not values).

+2
source

If you don't like code duplication, why not use a common table expression? eg

WITH x(a, b) AS 
        (
                select 1 a,1 b union all
                select 2,2 union all
                select 2,null union all
                select 3,3 union all
                select 3,null union all
                select 3,null
        ) 
select t1.a, t1.countdistinctb, t2.suma from
(
        select a,count(distinct b) countdistinctb from 
        x a
        where a.b is not null
        group by a
) t1
left join
(
        select a,sum(a) suma from 
        x a
        group by a
) t2 on t1.a=t2.a
+1
source

More articles:


All Articles