GROUP BY with HAVING clause does not return a row?

Question

Strange problem with a GROUP BY and HAVING clause on SQL Server 2014.

It's distinctly possible I've done something wrong, but I cannot make sense of this.

Here's the setup data:

create table #accounts(accountid int)
create table #data(accountid int, categoryid int, asofdate date, datavalue numeric(20, 10))

insert into #accounts
    values 
    (1),(2),(3)

insert into #data
    values 
    (1, 10, '1/31/2015', 0),
    (1, 10, '2/28/2015', 10),
    (1, 10, '3/31/2015', 20),
    (2, 10, '1/31/2015', 0),
    (2, 10, '2/28/2015', 15),
    (2, 10, '3/31/2015', 25),
    (3, 10, '1/31/2015', 0),
    (3, 10, '2/28/2015', 7),
    (3, 10, '3/31/2015', 12)

This returns a single row... for 1/31/2015. The only date with a total of zero

select categoryid, asofdate, sum(datavalue) as totalvalue
from #accounts a
inner join #data d
    on d.accountid = a.accountid
group by d.categoryid, d.asofdate
having sum(datavalue) = 0

Result is 10, '1/31/2015', 0

Yet, somehow the following does not return any rows... I'm basically asking it to give me the Max date from the first query. Why does this balk?

select categoryid, max(asofdate) as MaxAsOfDate, sum(datavalue) as totalvalue
from #accounts a
inner join #data d
    on d.accountid = a.accountid
group by d.categoryid
having sum(datavalue) = 0

drop table #accounts
drop table #data

Yet it returns no rows... baffled.

SQL Fiddle here:

http://sqlfiddle.com/#!6/5e44cb/1

Answer 1

Simply, because you are Grouping by d.categoryid - And there is no grouping by this column, which would return 0 for sum(datavalue): http://sqlfiddle.com/#!6/5e44cb/4 (it's 89)

If you could add your expected output to your post, we might be able to help you with the query you need.

Answer 2

By removing the GROUP BY asOfDate, you've removed any groups that have SUM()=0.

You should make your first query a derived table or CTE, and then do the MAX outside of it to get your desired results.