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.