CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
z00l
z00l

Reputation: 906

GROUP BY with summing null values to every group

I want to group values and sum them up by a category value - or none. For example, I have the following table:

+-------+----------+
| value | category |
+-------+----------+
|    99 |        A |
|    42 |        A |
|    76 |   [NULL] |
|    66 |        B |
|    10 |        C |
|    13 |   [NULL] |
|    27 |        C |
+-------+----------+

My desired result should look like this:

+-------+----------+
|   sum | category |
+-------+----------+
|   230 |        A |
|   155 |        B |
|   126 |        C |
|    89 |   [NULL] |
+-------+----------+

I tried a group by category but obviously this doesn't bring up the right numbers.

Any ideas?

I'm using SQL Server 2012.

EDIT: Ok, as requested, I can explain my intents and give my query so far, although that is not very helpful I think.

I need to sum all value for the given categories AND add the sum of all values without a category [=> NULL] So in my example, I would sum 

99 + 42 + 76 + 13 = 230 for category A
66 + 76 + 13 = 155      for category B
10 + 27 + 76 + 13 = 126 for category C
76 + 13 = 89            for no category

I hope that gives you an idea of my goal.

Query so far:

SELECT SUM([value]), [category]
FROM [mytable]
GROUP BY [category]

Upvotes: 2

Views: 2263

Answers (5)

paparazzo
paparazzo

Reputation: 45096

similar to levelonehuman put might be a little faster 

declare @countNull int = (select sum(textUniqueWordCount) from docSVsys where  mimeType is null);
select mimeType, sum(isnull(textUniqueWordCount, 0)) + @countNull  as [sum]  
from docSVsys 
where mimeType is not null
group by mimeType
union 
select null, @countNull;

Upvotes: 0

Matt from Philly
Matt from Philly

Reputation: 1

Maybe you just missed using the SUM built-in function? This should work:

SELECT
SUM(value) AS [sum], category
FROM
[YourTableHere]
GROUP BY category

Edit: Ah, I see what you are doing now. I was able to do it by joining a 2nd query with just the NULL sum, so the NULL sum comes back with every row. Then you can just add it in the final step.

SELECT
MainSet.sum + JustNulls.sum AS [sum], MainSet.category
FROM

(SELECT SUM(X.value) AS [sum], X.category FROM [YourTableHere] X 
WHERE X.category IS NOT NULL GROUP BY category
UNION SELECT 0, NULL) MainSet

FULL JOIN 

(SELECT SUM(Y.value) AS [sum], Y.category FROM [YourTableHere] Y 
WHERE Y.category IS NULL GROUP BY category) JustNulls ON 1=1

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

First calculate the sum of nulls then add it to each group:

DECLARE @t TABLE
    (
      value INT ,
      category CHAR(1)
    )

INSERT  INTO @t
VALUES  ( 99, 'A' ),
        ( 42, 'A' ),
        ( 76, NULL ),
        ( 66, 'B' ),
        ( 10, 'C' ),
        ( 13, NULL ),
        ( 27, 'C' )

;with cte as(select sum(value) as s from @t where category is null)
select category, sum(value) + s 
from @t
cross join cte
where category is not null
group by category, s

Another version:

;WITH cte AS(SELECT category, SUM(value) OVER(PARTITION BY category) + 
                 SUM(CASE WHEN category IS NULL THEN value ELSE 0 END) OVER() AS value 
             FROM @t)
SELECT DISTINCT * FROM cte WHERE category IS NOT NULL

Upvotes: 2

levelonehuman
levelonehuman

Reputation: 1505

You want to get the sum of the NULL category and add it to the value of the other (non-null) categories:

DECLARE @Table1 TABLE (Value int, Category varchar(1))

DECLARE @NullCategorySum int

INSERT INTO @Table1
    (Value, Category)
VALUES
    (99, 'A'),
    (42, 'A'),
    (76, NULL),
    (66, 'B'),
    (10, 'C'),
    (13, NULL),
    (27, 'C')

SELECT  @NullCategorySum = SUM(Value)
FROM    @Table1
WHERE   Category IS NULL

SELECT  SUM(t1.Value) 
            + CASE  
                WHEN Category IS NOT NULL THEN @NullCategorySum 
              END 
        AS SumValue, Category
FROM    @Table1 t1
GROUP BY Category

This outputs

SumValue    Category
89          NULL
230         A
155         B
126         C

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you want to add the NULL values to all the groups, then do something like:

with cte as (
      select category, sum(value) as sumv
      from t
      group by category
     )
select cte.category,
       (cte.sumv +
        (case when category is not null then coalesce(ctenull.sumv) else 0 end)
       ) as sumWithNulls
from cte left join
     cte ctenull
     on ctenull.category is null     -- or should that be `= '[NULL]'`?

This seems like a strange operation.

EDIT:

You can almost do this with window functions:

select category,
       (sum(value) +
        sum(case when category is null then sum(value) else 0 end) over ()
       ) as sumWithNulls
from t
group by category;

The problem is that NULLs get over counted for that category. So:

select category,
       (sum(value) +
        (case when category is not null
              then sum(case when category is null then sum(value) else 0 end) over ()
              else 0
        end
       ) as sumWithNulls
from t
group by category;

Upvotes: 0

Related Questions