CODEWITHSUNDEEP
sqloracle-databaseplsql
AlexK
AlexK

Reputation: 396

Trying to divide two counts in SQL

so I'm trying to do simple division and of course SQL, being super logical that it is, makes it impossible. What I am trying to do is this:

SELECT * FROM 
1 - ((SELECT COUNT(DISTINCT T.DID) FROM TGAMAZING T AS NUM) * 100 /
(SELECT COUNT(DISTINCT D.ID) FROM DIRECTORS D AS DENOM))

but how would I write this SQL (Oracle)?

Upvotes: 0

Views: 4696

Answers (2)

Caffé
Caffé

Reputation: 1171

Write the calculation as it were fields and use "FROM DUAL", as you don't want to query any of your tables.

SELECT  
  1 - ((SELECT COUNT(DISTINCT T.DID) FROM TGAMAZING T) * 100 /
  (SELECT COUNT(DISTINCT D.ID) FROM DIRECTORS D))
  AS RESULT
FROM DUAL

Reference: Selecting from the DUAL Table.

Upvotes: 2

JohnLBevan
JohnLBevan

Reputation: 24410

Oracle

SQL Fiddle: http://sqlfiddle.com/#!4/34298/8

Method 1:

SELECT 1 - (COUNT(DISTINCT DID) * 100 / COUNT(DISTINCT ID))
FROM TGAMAZING
cross join DIRECTORS;

Method 2:

SELECT 1 - 
    (
        (SELECT COUNT(DISTINCT DID) FROM TGAMAZING) * 100 /
        (SELECT COUNT(DISTINCT ID) FROM DIRECTORS)
    )
FROM DUAL;

SQL Server

SQL Fiddle: http://sqlfiddle.com/#!6/34298/3

Method 1

SELECT 1 - (COUNT(DISTINCT DID) * 100.0 / COUNT(DISTINCT ID))
FROM TGAMAZING
cross join DIRECTORS;

Method 2

SELECT 1 - 
    (
        (SELECT COUNT(DISTINCT DID) FROM TGAMAZING) * 100.0 /
        (SELECT COUNT(DISTINCT ID) FROM DIRECTORS)
    )

Upvotes: 4

Related Questions