Group by Week, How to Get Empty Weeks

Group by week, how to get empty weeks?

SQL cannot return rows that don't exist in some table. To get the effect you want, you will need a table Weeks (WeekNo INT) with one row per possible week of the year (which, IIRC, is either 53 or 54 possible weeks, depending on how you count).

Then, JOIN this table to your regular results with an OUTER JOIN to get the extra weeks added in.

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(date) as number 
    FROM YourTable RIGHT OUTER JOIN Weeks ON WEEK(YourTable.date) = Weeks.WeekNo

[Update]: Note the user of COUNT(date) rather than COUNT(*). SQL will not include NULL values in the date column when adding up the COUNT. Since the missing weeks will not have any dates in them, this will correctly give you 0 events for those weeks.

Group by week number including empty weeks and sum other column

 DECLARE @tmp table(dat datetime,
                    val float)

 insert into @tmp 
 values  ('15/04/2016' , 20459.92),
         ('29/05/2016',  7521.89),
         ('30/05/2016',  5963.61),
         ('31/05/2016',  3293.72),
         ('03/06/2016',  27413.20),
         ('04/06/2016',  8392.16),
         ('05/06/2016',  7789.46),
         ('05/06/2016',  11414.73),
         ('10/06/2016',  48893.46),
         ('11/06/2016',  14685.47),
         ('11/06/2016',  7030.03)

 SELECT  Weeknumb,
         ISNULL(sumvals,0) as weekval
 FROM 
  (SELECT DATEPART(ISOWK,DATEADD(wk,t2.number,'2016')) as Weeknumb
   FROM master..spt_values t2
   WHERE t2.type = 'P'
   AND t2.number <= 255
   AND YEAR(DATEADD(wk,t2.number,'2016'))=2016)allWeeks
   LEFT JOIN
  (SELECT  sum(val) as  sumvals,
           datepart(ISOWK,dat) as  weeks
   FROM @tmp
   GROUP BY datepart(ISOWK,dat) ) actualData
   ON weeks = Weeknumb
  ORDER BY Weeknumb asc

there you go. All weeks of 2016 with your values summed

SQL group by week include weeks where count is 0

Use generate_series():

SELECT w.week, COUNT(*) AS total
FROM (SELECT generate_series(min(DATE_TRUNC('week', created_at)),
                             max(DATE_TRUNC('week', created_at)),
                             interval '7 day'
                            ) as week
      FROM reviews
     ) w LEFT JOIN
     "reviews" 
     ON DATE_TRUNC('week', created_at) = w.week
GROUP BY w.week
ORDER BY w.week;

Group by week, display empty weeks - with where clause

I've modified some of your field names slightly. I'm not sure if this 100% meets your use-case, but if it doesn't, you should be able to figure it out from here. Basically, move your current WHERE condition into the ON clause.

SELECT p.fromz, count(distinct p.fromz), p.callDate, c.theDate FROM phones p
    RIGHT OUTER JOIN calendar c ON
    p.callDate = c.theDate AND p.toz = 2125555555
    GROUP BY c.theDate

SQLFiddle

Full Disclosure
This is not standards-compliant. For a compliant query, take a look at Ollie's answer and simply move the p.toz part from the WHERE to the ON to get the results you want.

How to groupby week from a given date range SQL Server and return count 0 if no data found for a week

You can use DATEPART, like:

DATEPART(week, submited_date)

I would advise using the week number and year number (similar to MySQL's WeekYear) in order to ensure the grouping is specific to that year's week.

Summarise by week, even for empty rows

The where clause WHERE (sales.transDate BETWEEN @fromDate AND @toDate) will remove any weeks without sales. You'll likely need to do a subquery to pull the transactions and then join that to your weeks table.

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
 (
    SELECT *
    FROM sales 
    WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
 ) sales
    ON Weeks.WeekNum = DATEPART(week, sales.transDate)
GROUP BY Weeks.WeekNum

---

Related Topics