CODEWITHSUNDEEP
sqlsql-server
we_are_all_in_this_together
we_are_all_in_this_together

Reputation: 541

How to keep 0 value entries when doing Count and GroupBy in SQL Server?

I have tried isnull but it's still leaving out certain bins of data if the count in that bin is 0. I am using group by to count the number of observations in each group. For groups with 0 observations, SQL does not print out a row for that group. I want a row for that group and a corresponding 0 value.

I have tried this: Keep zero values with GROUP BY and SUM in mysql

Thanks!

Edit with data:

Vector = [1, 2, 2, 3, 4, 5, 5, 9, 10, 10]

I want to count the number of observations in the above that fall into the bins:

[1-2, 3-4, 5-6, 7-8, 9-10]

The results should be

[3,2,2,0,3]

However I get

[3,2,2,3]

Upvotes: 0

Views: 611

Answers (2)

SteveC
SteveC

Reputation: 6015

Here's an approach which creates the bins as separate columns. Then the final string is created using CONCAT_WS and QUOTENAME. Something like this

declare @Vector         varchar(max)='1, 2, 2, 3, 4, 5, 5, 9, 10, 10';

with
range_cte(n) as (
    select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) v(n))
select quotename(concat_ws(',',
       sum(case when sc.value in(1, 2) then 1 else 0 end) ,
       sum(case when sc.value in(3, 4) then 1 else 0 end) ,
       sum(case when sc.value in(5, 6) then 1 else 0 end) ,
       sum(case when sc.value in(7, 8) then 1 else 0 end) ,
       sum(case when sc.value in(9, 10) then 1 else 0 end)),'[]')
from range_cte rc 
     left join string_split(@Vector, ',') sc on rc.n=sc.value;

Output

[3,2,2,0,3]

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

You could create "bin" derived table and LEFT JOIN to it:

SELECT bin.a, bin.b, COUNT(v.val) AS cnt
FROM (VALUES (1,2),(3,4),(5,6),(7,8),(9,10)) bin(a,b)
LEFT JOIN vector v
  ON v.val BETWEEN bin.a AND bin.b
GROUP BY bin.a, bin.b
ORDER BY bin.a, bin.b;

db<>fiddle demo

Output:

+----+-----+-----+
| a  | b   | cnt |
+----+-----+-----+
| 1  |  2  |   3 |
| 3  |  4  |   2 |
| 5  |  6  |   2 |
| 7  |  8  |   0 |
| 9  | 10  |   3 |
+----+-----+-----+

Upvotes: 1

Related Questions