SQL Group and Sum by Month - Default to Zero

SQL Group and Sum By Month - Default to Zero

In the past, I've solved a problem like this by creating a temporary table which will hold all dates needed:

    CREATE TABLE #AllDates (ThisDate datetime null)
    SET @CurrentDate = @StartRange

    -- insert all dates into temp table
    WHILE @CurrentDate <=  @EndRange
        BEGIN
            INSERT INTO #AllDates values(@CurrentDate)
            SET @CurrentDate = dateadd(mm, 1, @CurrentDate)
        END

Then, modify your query to join against this table:

SELECT      ALLItems.ItemId,
            SUM(COALESCE(Inventory.Qty, 0)) AS Individual_MonthQty,
            MONTH(#AllDates.ThisDate) AS Individual_MonthAsNumber,
            DATENAME(MONTH, #AllDates.ThisDate) AS Individual_MonthAsString
FROM        #AllDates
            JOIN (SELECT DISTINCT dbo.Inventory.ItemId FROM dbo.Inventory)  AS ALLItems ON 1 = 1
            LEFT JOIN Inventory ON DATEADD(dd, - DAY(Inventory.dadded) +1, Inventory.dadded) = #AllDates.ThisDate AND ALLItems.ItemId = dbo.Inventory.ItemId
WHERE       
            #AllDates.ThisDate >= @StartRange
AND         #AllDates.ThisDate <= @EndRange
GROUP BY    ALLItems.ItemId, 
            #AllDates.ThisDate

Then you should have a record for each month, regardless of whether it exists in Inventory.

GROUP BY & SUM of values with missing MONTHS

You need to move condtition to ON:

-- ...
SELECT 
  [Period] = CONVERT(VARCHAR(4),YEAR(d.d)) +'-'+ CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM d LEFT OUTER JOIN VE_STOCKTRANS AS o
  ON o.TRANSDATE >= d.d

  AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
  AND STOCKID = 6000 AND TRANSTYPE = 3553   -- here
GROUP BY d.d
ORDER BY d.d;

SUM with GROUP BY don't display zero sums

What the others have been trying to tell you is that you need a "list" of the CURR values you want to see in your results. Generally, these would come from another table in a properly normalized database. Do you have one? It seems not but it is worth asking. A properly normalized database would generally have one.

So how do you create this list dynamically? Let us assume that your existing table has at least one row for every CURR value you desire in your resultset - even if that row has a date that falls outside of your period of interest. We can use that to form this list and then outer join that list to your existing query that does the summing.

with curr_list as (select distinct CURR from dbo.XXX)
select curr_list.CURR, 
       sum(isnull(tbl.AMOUNT)) as TOPL 
from curr_list left join dbo.XXX as tbl 
   on curr_list.CURR = tbl.CURR 
   and tbl.[DATE] >= '20180101' 
   and tbl.[DATE] < '20180201'
group by curr_list.CURR
order by curr_list.CURR;

That should work assuming I made no typos. The CTE (named curr_list) creates the list of ID values that you want to see in your resultset. When you outer join that to your transaction data you will get at least one row for each CURR value. You then sum the amounts to aggregate those rows into a single row for each CURR value. Notice the change to the date criteria. Your original approach prevents the optimizer from using any useful indexes on that column.

If your existing table does not have a row for every value of CURR you want in your results, then you can simply change the cte and hardcode the values you desire.

How to group by month and return zero if no value for certain month?

Maybe this it's not the best way to do it, but it will solve your problem. As a quick soution:

SELECT 'January' AS mName, 1 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 1

UNION

SELECT 'February' AS mName, 2 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 2

UNION

...and go on

GROUP BY for each Month and return 0 or NULL if not month exists (MySQL)

If you have data for all months in the table -- but just not for that user -- then the simplest (although not most efficient) approach is conditional aggregation:

SELECT MONTH(date_point) as mon,
       SUM(CASE WHEN p.user_id = 1 THEN p.hours END) as recap
FROM pointage p
WHERE YEAR(date_point) = 2020 
GROUP BY MONTH(date_point) ;

Otherwise, you need a list of the months, which can be generated in many ways -- a calendar table, recursive CTE, explicitly:

select m.mon, sum(p.hours)
from (select 'Jan' as mon, 1 as mm union all
      select 'Feb' as mon, 2 as mm union all
      . . .
     ) m left join
     pointage p
     on p.date_point >= '2020-01-01' and '2021-01-01' and
        p.user_id = 1 and
        month(p.date_point) = m.mm
group by m.mon
order by min(m.mm);

Sum of value by month where data contains start and end dates

You just need to build a list of dates between MIN(StartDate) and today, rest is straight forward:

DECLARE @TestProject TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Project NVARCHAR(50) NOT NULL, StartDate DATE NOT NULL, EndDate DATE NULL, Value INT NOT NULL);
INSERT INTO @TestProject VALUES
('AAA', '2018-01-01', NULL, 100),
('AAA', '2018-04-12', NULL, 50),
('BBB', '2018-01-01', '2018-03-01', 200),
('BBB', '2018-01-01', NULL, 20);

WITH cte AS (
    SELECT DATEADD(DAY, 1, EOMONTH((SELECT MIN(StartDate) FROM @TestProject), -1)) AS ym
    UNION ALL
    SELECT DATEADD(MONTH, 1, ym)
    FROM cte
    WHERE DATEADD(MONTH, 1, ym) <= CURRENT_TIMESTAMP
)

SELECT ym, Project, SUM(Value)
FROM cte
LEFT JOIN @TestProject ON DATEADD(DAY, 1, EOMONTH(StartDate, -1)) <= ym AND (
    EndDate IS NULL OR ym <= DATEADD(DAY, 1, EOMONTH(EndDate, -1))
)
GROUP BY ym, Project

In the above example, DATEADD(DAY, 1, EOMONTH(expr, -1)) function is used to generate start of month for specified date.

---

Related Topics