1

Say I had two tables in SQL. Now I would like to get the quotient of the count of table 1 and count of table 2. How can I do that?

In Short:

(# of rows in table 1) / (# of rows in table 2)

EDIT: This is what I tried:

SELECT COUNT(t1.a) / COUNT(t2.a)
FROM table1 t1, table2 t2
Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
user2426316
  • 7,131
  • 20
  • 52
  • 83

3 Answers3

1

Here's one way to get the result:

SELECT c1.cnt / c2.cnt AS q
  FROM ( SELECT COUNT(1) AS cnt
           FROM table1
       ) c1
 CROSS
  JOIN ( SELECT COUNT(1) AS cnt
           FROM table2
       ) c2

Another way to get an equivalent result:

SELECT (SELECT COUNT(1) FROM table1) / (SELECT COUNT(1) FROM table2) AS q

I would prefer the first query if I also needed to return the counts from the tables as separate columns in the resultset, for example:

 SELECT c1.cnt          AS table1_count
      , c2.cnt          AS table2_count
      , c1.cnt / c2.cnt AS q
   FROM ...
spencer7593
  • 106,611
  • 15
  • 112
  • 140
-1
with 
    Ctable1 as
        (select count(*) as num1 from table1),
    Ctable2 as
        (select count(*) as num2 from table2)
select num1 / num2 as quotient
from Ctable1,Ctable2

Remember:

  • When you count column, rows with "NULL" data will NOT count. (If you use Oracle, you can use count(a.*)
  • Int division in sql like most languages, returns int. (5/2 = 2 and not 2.5).
Adam Lear
  • 38,111
  • 12
  • 81
  • 101
Yaron
  • 233
  • 1
  • 14
  • This does not return the right answer. The big problem is the CROSS JOIN operation between the two tables, the number of rows processed by the COUNT() aggregates is the same. – spencer7593 Nov 29 '13 at 00:30
-1

Try this:

SELECT COUNT(table1.column) as 'Table 1 Count'
,COUNT(table2.column) as 'Table 2 Count'
,COUNT(table1.column) / COUNT(table2.column) as 'Quotient'
FROM table1, table2
abarbaneld
  • 61
  • 1
  • 9
  • 1
    This does not return the right answer. The big problem is the CROSS JOIN operation between the two tables. The number of rows processed by the COUNT() aggregates is the same. Also, rows with a NULL in the count expression will not be included in the count. – spencer7593 Nov 29 '13 at 00:33