count distinct rollup cube - 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)

count distinct rollup cube

Author

Topic

clir
Starting Member

6 Posts

Posted - 2005-08-17 : 20:36:15

Hello,

Using rollup I want to count the number of rows of a table called Table1 which is LEFT JOINED with a table called Table2.

The problem is that we can have more than 1 rows in Table2 that matched 1 row in Table1. As a consequence the count of rows in table1 is bigger than the real number of rows contained in table1.

For example:

Select COUNT(Table1.employeeID), Table1.country
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.phone_number = Table2.phone_number
GROUP BY Table1.country WITH ROLLUP

that works but give me a COUNT higher than the truth because I can have several times the same phone number in Table2!

COUNT(DISTINCT...) would work if WITH ROLLUP not used BUT I want absolutely to use ROLLUP.
Does someone know a workaround?

Any help much appreciated.

andy8979
Starting Member

36 Posts

Posted - 2005-08-18 : 03:49:26

Try using Derived Tables.
For eg:

Select COUNT(Table1.employeeID), Table1.country
FROM Table1
LEFT OUTER JOIN (Select Distinct Phone_number From Table2) As T2 ON Table1.phone_number = T2.phone_number
GROUP BY Table1.country WITH ROLLUP

Or other way around
Select COUNT(T.employeeID), T.country From
(Select distinct Table1.employeeid, Table1.country
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.phone_number = Table2.phone_number) As T
GROUP BY T.country WITH ROLLUP

I haven't run the queries but they should work. Hope this helps.

Anuj.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources