CODEWITHSUNDEEP
sqlsql-servergroup-by
ggnoredo
ggnoredo

Reputation: 821

Replacing Non-Exist Group By Results

I need to run a query like this:

select SUM(TONS) as TONS
from MyView 
where Date between '01.01.2017' and '12.31.2017' 
group by DATEPART(MONTH, Date)
order by DATEPART(YEAR, Date) asc

The problem is if there is no data on 3rd month it returns 11 rows in total instead of 12. How can I replace missing dates with value of 0?

Upvotes: 0

Views: 41

Answers (2)

neer
neer

Reputation: 4082

You can left join the result with months. 

  SELECT
        C.M YourMonth,
        COALESCE(Result.TONS, 0) AS TONS
    FROM
    (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) C(M) LEFT JOIN 
    (
        SELECT 
            DATEPART(MONTH, T.Date) YourMonth,
            SUM(T.TONS) AS TONS
        FROM    
            MyView  T 
        WHERE Date BETWEEN '01.01.2017' and '12.31.2017'
        GROUP BY    
            DATEPART(MONTH, T.Date)
    ) Result ON C.M = Result.YourMonth

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

You could create a calendar table and join to that, or you can use an adhoc calendar like so:

declare @fromdate date = '20170101';
declare @thrudate date = '20171231';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate,@thrudate)+1)
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo 
                 cross join n as tenK  cross join n as hundredK
  order by [Date]
)
, cal as (
  select
      [Date]
    , [Month]=month([Date])
    , [Year] =year([Date])
  from dates
)

select
    c.[Year]
  , c.[Month]
  , Tons = sum(v.Tons)
from cal c 
  left join MyView v
    on c.[Date] = v.[Date]
where c.[Date] >= '20170101'
  and c.[Date] <= '20171231'
group by c.[Year], c.[Month]
order by c.[Year], c.[Month]

calendar and numbers tables reference:

Upvotes: 3

Related Questions