How to Return Empty Groups in SQL Group by Clause

How to return empty groups in SQL GROUP BY clause

Yes, construct an expression that returns the ordertotal for adhoc only, and 0 for the others, and another one that does the opposite, and sum those expressions. This will include one row per location with two columns one for adhoc and one for Contracted...

 SELECT Location,  
     Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) AdHoc,
     Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal  End) Contracted
 FROM Orders 
 GROUP BY Location

if you reallly want separate rows for each, then one approach would be to:

 SELECT Location, Min('AdHoc') ContractStatus,
     Sum(Case When Contract_ID Is Null 
              Then OrderTotal Else 0 End) OrderTotal
 FROM Orders 
 GROUP BY Location
 Union
 SELECT Location, Min('Contracted') ContractStatus,
     Sum(Case When Contract_ID Is Null 
              Then 0 Else OrderTotal  End) OrderTotal
 FROM Orders 
 GROUP BY Location
 Order By Location

Adding empty groups to a group by clause (getting all 24 hours of the day represented)

You can use a recursive CTE (or other method) to generate 24 hours:

with hours as (
      select 0 as h
      union all
      select h + 1
      from hours
      where h + 1 < 24
    )
select hours.h as [Hour],
       sum(case . . . else 0 end) as [Calls Answered]
from hours left join
     [table] t
     on hours.h = datepart(hour, [Start Time])
group by hours.h
order by hours.h;

The else 0 is one way to (probably) ensure that sum() returns 0 instead of NULL. If you already have an else, you need to be careful, because some hours might not have any matches in your table.

SQL Group By including empty rows

SELECT pla.PlaceID, COUNT(peo.PersonID)
FROM Places AS pla LEFT OUTER JOIN People as peo ON peo.PlaceID = pla.PlaceID
GROUP BY pla.PlaceID

EDITed question:

Assuming there is always a FooConfig entry, we'll drop the LEFT JOIN to that table (as it'll always be there). We can then include the extra criteria in the join to the Foo table:

SELECT
    ft.FooTypeID, COUNT(f.FooID)
FROM FooType as ft
  JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID
  LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND
    DateDiff(day, GetDate(), f.Date) > 0 AND
    DateDiff(day, GetDate(), f.Date) < fc.Days
GROUP BY ft.FooTypeID

If the FooConfig table is optional, then the extra date criteria can't be used (as they would always evaluate to false) - so we'd have to do something like:

SELECT
    ft.FooTypeID, COUNT(f.FooID)
FROM FooType as ft
  LEFT OUTER JOIN FooConfig fc ON ft.NotificationConfigID = fc.FooConfigID
  LEFT OUTER JOIN Foo f ON ft.FooTypeID = f.FooTypeID AND
    (
      (DateDiff(day, GetDate(), f.Date) > 0 AND
       DateDiff(day, GetDate(), f.Date) < fc.Days) 
      OR
      (fc.Days IS NULL)
    )
GROUP BY ft.FooTypeID

Return 0 in GROUP BY when COUNT(*) is NULL

So I flipped the aggregates from the edit to my original post and now it's working:

Query

SELECT
        CAST(a.IndexedDate as varchar) as dt,
        COUNT(EventType) AS Logins
FROM 
        (
        SELECT DISTINCT(IndexedDate)
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        ) a
FULL OUTER JOIN (
        SELECT *
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        AND EventType = 'Login'
        ) b
ON 
        a.IndexedDate = b.IndexedDate
GROUP BY
        a.IndexedDate
ORDER BY
        a.IndexedDate DESC

Results

2016-09-13    41
2016-09-12    31
2016-09-11    0
2016-09-10    0
2016-09-09    15
2016-09-08    36

Note that I had to replace COUNT(*) with COUNT(EventType) so it wouldn't count the date from the aggregate which was resulting in a 1.

Include Empty Group in GROUP BY (MYSQL QUERY)

Since your grouping fields seem to have no relation to one another other than via the "things" they are associated with, you will need to synthesize all possible groupings; you can then left join the "things" to that superset. Something like this:

SELECT lvl.lvl_name as Level, slvl.sublvl_name as SubLevel, sta.stat_name as Status
   , COUNT(thg.Id_thing) as Thing_Total
FROM tb_level lvl
JOIN tb_sublevel slvl
JOIN tb_status sta 
LEFT JOIN tb_things as thg 
   ON thg.id_lvl=lvl .id_lvl 
   AND thg.id_slvl=slvl.id_slvl
   AND thg.id_stat= sta.id_stat
GROUP BY Level, SubLevel, Status

However, if there are any "things" without some grouping references (like a thing with id_lvl and id_slvl set, but an id_stat that is null), those things will be omitted from the results.

SQL Server group by absorb null and empty values

You can't have a field in the SELECT statement unless it's part of the GROUP BY clause or used for aggregation. The question and desired output shows that the rows should be grouped by name, which means all other fields (ID,amount, comments) should be aggregated.

The question doesn't specify how the IDs should be aggregated, or which comments should appear. Aggregating strings is only possible using functions like MIN/MAX in all SQL Server versions up to 2016. SQL Server 2017 added STRING_AGG to concatenate strings. In earlier versions people have to use one of many string aggregation techniques that may involve XML or SQLCLR functions.

In SQL Server versions the desired output can be produced by

SELECT MIN(ID) as ID,name,sum(amount) as Amount, max(comment) as comments
from #table1 
group by name

This produces the desired output :

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

This assumes that there is only one non-empty comment. The question doesn't specify something different though.

In SQL Server 2017 multiple comments can be concatenated with STRING_AGG :

SELECT MIN(ID) as ID,name,sum(amount) as Amount, STRING_AGG(comment,' ') as comments
from table1 
group by name

Given the question's data, this will also produce the desired output.

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

Group By Date With Empty Groups

Essentially what you're asking is to join your table to a "table" of dates. The date table would have no gaps and you would group on the date value. So how to create a table of dates?

In SQL for Smarties, it's suggested that you keep a table of integers around for cases when you need a gapless sequence to join to. Then you can select whatever sequence you need by joining your table to it.

So if you had an integer table with values going as many days back from NOW() as required you might do the following:

SELECT DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY) AS thedate, 
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS logins
FROM i LEFT JOIN dual ON (DATE_SUB(NOW(), INTERVAL i.intvalue DAY)= day)
GROUP BY DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY)
ORDER BY i DESC;

ETA, for mysql:

//create an integer table

create table i(i integer not null primary key);
insert into i values (0),(1),(2) ... (9);

if I need 0-99 consecutive numbers:

SELECT 10*t.i + u.i AS number
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY number;

if I need consecutive dates:

SELECT date_sub(curdate(), interval (10*t.i + u.i) DAY) as thedate 
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY thedate;

Empty GROUP BY in PostgreSQL

To aggregate all rows, you don't need to form groups with GROUP BY and can can just omit the GROUP BY clause. The manual:

If there are aggregate functions but no GROUP BY clause, the query is
treated as having a single group comprising all the selected rows.

Or (if you are building the query string dynamically) you can use any constant expression like:

...
GROUP BY true

You can't use GROUP BY 1 for this purpose because integer constants serve as positional references (ordinal numbers) in GROUP BY:

• Select first row in each GROUP BY group?

---

Related Topics