CODEWITHSUNDEEP
sqloracle-database
Corbuk
Corbuk

Reputation: 700

How to divide two SELECT statements to get a percentage with SQL

I'm trying to calculate the the percentage of items sent by royal mail from the delivery table. 

(SELECT post_method
FROM delivery_tbl
WHERE post_method = 'RM') 

/

SELECT post_method
FROM delivery_tbl;

I get an error saying

"SQL command not properly ended".

Any help on this would be great! Thanks!

Upvotes: 3

Views: 8625

Answers (6)

EzLo
EzLo

Reputation: 14189

You are doing a division of 2 queries, but to properly display the result, you need to add a SELECT statement of the division.

This is wrong:

-- Result: SQL command not properly ended
(SELECT 100 FROM DUAL) / (SELECT 50 FROM DUAL)

This is OK:

-- Result: 2
SELECT
    (SELECT 100 FROM DUAL) / (SELECT 50 FROM DUAL)
FROM 
    DUAL

You should also try to add column names so it's clearer:

-- ResultOfDivision: 2
SELECT
    (SELECT 100 FROM DUAL) / (SELECT 50 FROM DUAL) ResultOfDivision 
FROM
    DUAL

Please keep in mind that when doing these type of divisions (or any arithmetic operation) between subqueries, each subquery must return 1 row. If they don't then the SQL engine wouldn't know which row to use and throws an error.

-- Result: single-row subquery returns more than one row.
SELECT
    (SELECT 100 FROM DUAL) / (SELECT SomeColumn FROM YourTableWithManyRows) ResultOfDivision 
FROM 
    DUAL

For this last example, the correct expression would be the following (for each row).

SELECT
    (100 / SomeColumn) ResultOfDivision 
FROM
    YourTable
WHERE
    SomeColumn <> 0 -- Prevent division by 0!!

For your case, seems that you are trying to see the following:

-- Make sure SELECT COUNT(1) FROM delivery_tbl isnt 0!

SELECT
    post_method,
    COUNT(post_method) * 100 / (SELECT COUNT(1) FROM delivery_tbl) 100perc, -- 100 based percentage
    COUNT(post_method) / (SELECT COUNT(1) FROM delivery_tbl) 1perc -- 1 based percentage
FROM
    delivery_tbl
GROUP BY
    post_method

Upvotes: 2

Boneist
Boneist

Reputation: 23578

You can do this using conditional aggregation, like so:

SELECT round (100 * (count(case when post_method = 'RM' then 1 end) / count(*)), 2) royal_mail_items_percentage
FROM   delivery_tbl;

I've included the round function as it looks like you only want to display up to 2 decimal places.

Doing the query using conditional aggregation means you only have to query the table once, so it should be more performant than the other answers, which need to hit the table more than once.

Upvotes: 4

Fahim.csedu
Fahim.csedu

Reputation: 11

Use the sql: 

select (
(select sum(post_method) from delivery_tbl where post_method = 'RM') /
(select sum(post_method) from delivery_tbl)) * 100 as "royalperc" from dual ;

Upvotes: 0

Seif Hatem
Seif Hatem

Reputation: 1473

SELECT ((SELECT post_method FROM delivery_tbl Where post_method = 'RM')/ (SELECT post_method FROM delivery_tbl))*100 as royalperc

Updated query to get counts instead

 SELECT ((SELECT COUNT(post_method) FROM delivery_tbl Where post_method = 'RM')/ (SELECT COUNT(post_method) FROM delivery_tbl))*100 as royalperc

I believe you should use the second query

Upvotes: 1

Ruslan L.
Ruslan L.

Reputation: 436

Add a SELECT statement at the beginning:

SELECT ((SELECT post_method FROM delivery_tbl Where post_method = 'RM') / (SELECT post_method FROM delivery_tbl));

Upvotes: 0

Ibo
Ibo

Reputation: 4309

 Select post_method, (Count(post_method)* 100 / (Select Count(*) From 
 delivery_tbl)) as percentage
 From delivery_tbl
 Where post_method = 'RM'
 Group By post_method

Upvotes: 4

Related Questions