Having condition

I have query:
SELECT name,company,sum(quantity) as quantity from table
GROUP BY name,company WITH ROLLUP HAVING name is not null
But in result set I get also resulting rows with name is null:
name company quantity
--
NULL NULL 100
NULL company1 2
NULL company2 5
...
...
name 1 company1 2
...
Any idea why?
How can I remove summarazing rows with name is null in result set?
I thought that having should work?
regards,Simonwhere name is not null
Having is intended for filtering aggregate values, not column values.
Admittedly, I would have expected it to work anyway. Maybe the rollup is
affecting it. I don't generally have a use for rollup, so I'm not really
sure how it functions...
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23N4vH%23HZGHA.2136@.TK2MSFTNGP05.phx.gbl...
> I have query:
> SELECT name,company,sum(quantity) as quantity from table
> GROUP BY name,company WITH ROLLUP HAVING name is not null
> But in result set I get also resulting rows with name is null:
> name company quantity
> --
> NULL NULL 100
> NULL company1 2
> NULL company2 5
> ...
> ...
> name 1 company1 2
> ...
> Any idea why?
> How can I remove summarazing rows with name is null in result set?
> I thought that having should work?
> regards,Simon
>|||Hi,
Check WITH ROLLUP clause in BOL.
The number of summary rows in the result set is determined by the number of
columns included in the GROUP BY clause. Each operand (column) in the GROUP
BY clause is bound under the grouping NULL and grouping is applied to all
other operands (columns). Because CUBE returns every possible combination of
group and subgroup, the number of rows is the same, regardless of the order
in which the grouping columns are specified.
Tomasz B.
"Jim Underwood" wrote:

> where name is not null
> Having is intended for filtering aggregate values, not column values.
> Admittedly, I would have expected it to work anyway. Maybe the rollup is
> affecting it. I don't generally have a use for rollup, so I'm not really
> sure how it functions...
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23N4vH%23HZGHA.2136@.TK2MSFTNGP05.phx.gbl...
>
>|||Thanks. I looked it up after making the last post, and tested it out.
On SQL 2000 the having clause works fine with this same logic. Granted, my
tables are different, but the following excluded null values from the result
set.
select description, bogusID, count(*), sum(random_id) from my_table
group by description, bogusID with rollup
having description is not null.
If I remove the having, then I get back rows where description is null, with
the having these rows are excluded. However, in my test the having
description is not null also removed the grand summary at the end of the
report. This is as expected, and the grand summary row is there if I use
where instead of having.
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:2E2D3DBB-EA47-4E7F-BBB5-73C86261AA95@.microsoft.com...
> Hi,
> Check WITH ROLLUP clause in BOL.
> The number of summary rows in the result set is determined by the number
of
> columns included in the GROUP BY clause. Each operand (column) in the
GROUP
> BY clause is bound under the grouping NULL and grouping is applied to all
> other operands (columns). Because CUBE returns every possible combination
of
> group and subgroup, the number of rows is the same, regardless of the
order
> in which the grouping columns are specified.
> Tomasz B.
> "Jim Underwood" wrote:
>
is
really|||I meant to ask in my last response...
What version of SQL Server are you using, and could you post table DDL and
the full query that you are executing. If what you already posted is the
complete query, then just say so.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23N4vH%23HZGHA.2136@.TK2MSFTNGP05.phx.gbl...
> I have query:
> SELECT name,company,sum(quantity) as quantity from table
> GROUP BY name,company WITH ROLLUP HAVING name is not null
> But in result set I get also resulting rows with name is null:
> name company quantity
> --
> NULL NULL 100
> NULL company1 2
> NULL company2 5
> ...
> ...
> name 1 company1 2
> ...
> Any idea why?
> How can I remove summarazing rows with name is null in result set?
> I thought that having should work?
> regards,Simon
>|||The only reliable test you can make in HAVING with ROLLUP/CUBE
is based on the GROUPING(column) test.So in this example you can test
HAVING GROUPING([name])=0/1
HAVING GROUPING(company)=0/1
HAVING GROUPING([name])=0/1 and/or GROUPING(company)=0/1
0 will test for null,1 a non null if i remember:)
Of course you make as complex a HAVING as you want using the GROUPING()
statements ie.
GROUPING([name])+GROUPING(company)=0
Of course the big implication of all this is that a temp table or derived
table
must be used for aggregate testing.Silly:)
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23N4vH%23HZGHA.2136@.TK2MSFTNGP05.phx.gbl...
>I have query:
> SELECT name,company,sum(quantity) as quantity from table
> GROUP BY name,company WITH ROLLUP HAVING name is not null
> But in result set I get also resulting rows with name is null:
> name company quantity
> --
> NULL NULL 100
> NULL company1 2
> NULL company2 5
> ...
> ...
> name 1 company1 2
> ...
> Any idea why?
> How can I remove summarazing rows with name is null in result set?
> I thought that having should work?
> regards,Simon
>