Showing posts with label table2. Show all posts
Showing posts with label table2. Show all posts

Thursday, March 29, 2012

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.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

Razvansql

Thursday, March 22, 2012

Count 4 tables

I am trying to get a count of rows in 4 tables at once.

Output:

Table1 Table2 Table3 Table4

12345 143 892626 28428

Or maybe even

Table Count

Table1 12345

Table2 143

Table3 892626

Table4 28428

Thanks,

Cory

here it is...

Select

(Select Count(*) From Table1) [Table1],

(Select Count(*) From Table2) [Table2],

(Select Count(*) From Table3) [Table3],

(Select Count(*) From Table4) [Table4]

--Or

Select 'Table1' Table, Count(*) Count From Table1

Union ALL

Select 'Table2' Table, Count(*) Count From Table2

Union ALL

Select 'Table3' Table, Count(*) Count From Table3

Union ALL

Select 'Table4' Table, Count(*) Count From Table4

|||

Would this do the trick?

SELECT 'Table1' AS table, COUNT (*) AS count FROM table1 UNION

SELECT 'Table2', COUNT (*) FROM table2 UNION

SELECT 'Table3, COUNT (*) FROM table3 UNION

SELECT 'Table4', COUNT (*) FROM table4

|||

At always better option to use UNION ALL rather than UNION.