CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user20638149
user20638149

Reputation:

Combine two SELECT statements to create output in ORACLE SQL DEVELOPER

So I'm trying to combine the output of these two statements:

SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER WHERE MILES BETWEEN 3 AND 5;

This total gives me 8

SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER;

*This total gives me 20 (total number of rows in my database)

This gives me an answer of who from my database lives within 3 and 5 miles, where miles is its own column.

I would like to output the answer to a percentage of my total database eg. 8/20 * 100 * = 40%

SELECT
  (SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER WHERE MILES BETWEEN 3 AND 5) / 
  (SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER) * 100 FROM CUSTOMER ;

But this gives me 20 rows of "40" which is the correct answer, I just don't want 20 rows of it.

Upvotes: 1

Views: 88

Answers (2)

MT0
MT0

Reputation: 168096

Do it all in a single query using conditional aggregation:

SELECT COUNT(CASE WHEN miles BETWEEN 3 AND 5 THEN customer_id END)
       / COUNT(customer_id) * 100 AS percentage
FROM   customer;

If you use sub-queries then you will access the table twice and it will be less efficient.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

The final FROM should be from DUAL (it contains 1 row only), not CUSTOMER (which, apparently, contains 20 rows).

SELECT (SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER WHERE MILES BETWEEN 3 AND 5) /
       (SELECT COUNT (CUSTOMER_ID) FROM CUSTOMER) * 100 as result
FROM DUAL;         --> here

Upvotes: 1

Related Questions