471,088 Members | 1,229 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,088 software developers and data experts.

count only records from left table ROLLUP or CUBE


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

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
1 2400
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:


Aug 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Burt | last post: by
2 posts views Thread by gregfocker | last post: by
4 posts views Thread by max | last post: by
22 posts views Thread by MP | last post: by
5 posts views Thread by Kevin | last post: by
2 posts views Thread by kiss07 | last post: by
reply views Thread by Tonkuma | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.