rollup your troubles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

rollup your troubles

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-13 : 13:07:53

Robert writes "Consider the following query:

select Name,Count(*) as Count from Client group by Name

Name Count
---------- -----------
Client A 10
Client B 20
Client C 30

Appling ROLLUP to this ...

select Name,Count(*) as Count from Client group by Name with rollup

Name Count
---------- -----------
Client A 10
Client B 20
Client C 30
NULL 60

... which is exactly what is expected.

However if I then apply the HAVING operator to the above:

select Name,Count(*) as Count from Client group by Name with rollup having count(*) > 25

this gives ...

Name Count
---------- -----------
Client C 30
NULL 60

This may be valid (from an academic perspective?) but it is not the business result I need. In the above context I would expect (require) a rollup total of 30.

Any clues as to the correct way to resolve the above gratefully accepted."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 13:27:17


select
    a.name,
    sum(a.thecount)
from (
    select
        name,
        count(*) as thecount
    from
        client
    group by 
        name
    having count(*) > 3) a
group by
    a.name
with rollup

Rollup operates before the having, so you have to force it a bit....

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources