473,382 Members | 1,258 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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
1 2524
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Burt | last post by:
MS has been nice enough to add the Cube and Rollup operators so I can have totals with my results. But out of shear meanness they won't let me use them unless I use a Group By clause in my...
0
by: c.le_roq | last post by:
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...
2
by: gregfocker | last post by:
I have three tables: table1 has a one-to-many relationship with both table2 and table3. When I do a left join to get a count from table2, it works: SELECT table1.field1, COUNT(table2.field1) as...
2
by: Lauren Quantrell | last post by:
I am using a stored procedure as the recordsource on an MS-Access2000 form: Forms!frmName.RecordSource = "dbo.myStoredProcedure" The stored procedure creates a temp table #Contacts and then...
4
by: max | last post by:
I am beginning to learn sql and need some help. I have a table of customers with their addresses. Let's say I want to run a query returning the number of customers whose last name is "Smith" by...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
5
by: Kevin | last post by:
Using a base table, a MQT table was created. With optimization - when querying the base table with calcuation that are already completed in the MQT - I would assume the optimizer would use the MQT...
2
kiss07
by: kiss07 | last post by:
Dear Friends, I have an some question please clarify . 1)what's the difference between rollup and cube in sql? 2) what are ome restriction on procedure and function? 3)is possible ...
0
by: Tonkuma | last post by:
Download a Summary of the DB2 for i 6.1 (i5/OS V6R1) Enhancements (109 KB): http://www-03.ibm.com/systems/i/software/db2/pdf/db2_v6r1_enhancements.pdf On January 29, 2008, IBM announced a new...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.