467,923 Members | 1,170 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

count only records from left table ROLLUP or CUBE

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
  • viewed: 2285
Share:
1 Reply
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

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
kiss07
2 posts views Thread by kiss07 | last post: by
reply views Thread by Tonkuma | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.