count only records from left table ROLLUP or CUBE

c.le_roq

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?

Help much appreciated.

Aug 18 '05 #1

Razvan Socol

In your example, the workaround is very easy: remove the join with
Table2, because Table2 is not used at all in the query (there are no
conditions on this table, no columns selected from it; the only effect
of joining this table is the higher count, that you do not want).

Please post a query that is closer to your requirements, along with
DDL, sample data and expected results, as documented in:
http://www.aspfaq.com/etiquette.asp?id=5006

Razvan

Aug 18 '05 #2