CODEWITHSUNDEEP
mysqlsql
Ahek
Ahek

Reputation: 87

Selecting the percentage of a column

What I want to do is select a certain percentage of a column that satisfies a certain condition. I searched for it on this website already and found an answer, but it didn't work when I used it myself. I was wondering what I was doing wrong

SELECT Total FROM(
 SELECT Collected FROM (
  SELECT COUNT(status) FROM Requests AS Collected
   WHERE status = "A") / COUNT(status)) 
FROM Requests

As you can see here I want the percentage that has as status "A". Could someone tell me what goes wrong here?

Edit: Thanks for the feedback for asking a question. What I have is the table requests. I want to know the percentage that has status 'A'.

Request table sample data:

+--------+--------+
| Number | Status |
+--------+--------+
|      1 | A      |
|      2 | B      |
|      3 | B      |
|      4 | A      |
|      5 | B      |
+--------+--------+

Desired result:

+-----------------+
| Percentage_Of_A |
+-----------------+
|            40.0 |
+-----------------+

Error: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ COUNT(status)) FROM Requests' at line 4

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The simplest way to do this is with average:

SELECT AVG(CASE WHEN status = 'A' THEN 100.0 ELSE 0 END) as percentage_of_a
FROM Requests;

This is equivalent to, but shorter than:

SELECT SUM(CASE WHEN status = 'A' THEN 100.0 ELSE 0 END) / COUNT(*) as percentage_of_a
FROM Requests;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You seem to want the number of rows with status A, then the number of all rows and then their ratio.

select
  count(*) as total,
  count(case when status = 'A' then 1 end) as collected,
  count(case when status = 'A' then 1 end) * 100.0 / count(*) as percentage
from requests;

Your syntax errors explained:

SELECT Total -- there is no column called total in the subquery
FROM
(
  SELECT Collected -- there is no column called expression in the subquery
  FROM 
  (
    SELECT COUNT(status) -- This is okay. You could also simply use COUNT(*).
    FROM Requests AS Collected -- You alias the table as collected here, not the count expression
    WHERE status = "A" -- "A" would be a column (or column alias). A string literal has single quotes instead: 'A'.
  ) / COUNT(status) -- We are at the end of the FROM clause here. You cannot use / in the FROM clause.
) 
FROM Requests -- We are still in the main query, which has a FROM clause already. It is not allowed to have two FROM clauses.

Upvotes: 1

Related Questions