a query displaying 0 instead of not showing

• ahsal

  SSC Enthusiast

  Points: 105

  More actions

  November 17, 2019 at 1:25 pm

  #3698325

  hello,

  the following query is displaying the result i want except i want it to show 0 for each month with non production.

  SELECT DATENAME(MONTH, DATEADD(M, MONTH(PolicyDetails.IssuedDate), - 1)) AS Month, SUM(PolicyDetails.Premium) AS TotalProduction, DATENAME(YEAR, PolicyDetails.IssuedDate) AS Year FROM PolicyDetails INNER JOIN Clients ON PolicyDetails.ClientId = Clients.ClientId WHERE (Clients.Username = @Username) GROUP BY MONTH(PolicyDetails.IssuedDate), DATENAME(YEAR, PolicyDetails.IssuedDate)

  Month            ||  Total Production -$$

  2019 - August     ||            45.00

  2019 - October   ||            45.00

  in this table i want to show "2019 - September" with Total Production = 0 instead of displaying nothing. How ??

• Joe Torre

  SSChampion

  Points: 10239

  More actions

  November 17, 2019 at 2:13 pm

  #3698326

  You need a "table" with the months with the range of months.

  drop table if exists sales;
  create table sales(DateOfSale date, Price smallmoney);
  insert into sales values('20190802', 45),('20191015', 45);
  with months as(select mo from (values(8),(9),(10),(11)) mos(mo))
  select COALESCE(SUM(s.Price), 0) total, m.mo MonthOfSale
  from sales s
  right join months m on DATEPART(mm, s.DateOfSale) = m.mo
  group by mo;

  Attachments:

  You must be logged in to view attached files.