CODEWITHSUNDEEP
mysql
Paktros
Paktros

Reputation: 72

MySQL - Subquery returns more than 1 row

Firstly, here is an example of a table that I'm trying to manipulate :

##################################
#             TABLE_A            #
##################################
+----------+----------+----------+
|    ID    | COLUMN_A | COLUMN_B |
+----------+----------+----------+
|     1    |    24    |     1    |
|     2    |    53    |     1    |
|     3    |    12    |     1    |
|     4    |    87    |     2    |
|     5    |    63    |     2    |
|     6    |    55    |     3    |
|     7    |    39    |     3    |
|     8    |    47    |     3    |
|     9    |    85    |     3    |
+----------+----------+----------+

I want to calculate the percentage of each values of the column_a when the column_b has the same value.

For example, when column_b = 1, 24+53+12=89 so we will get the following percentages :

This is working when I use "WHERE" to indicate the value of the column_b as you can see with this request:

SELECT ROUND(
    (column_a*100) / (SELECT SUM(column_a) 
                      FROM TABLE_A 
                      WHERE column_b=1)
            ) AS RESULT 
FROM TABLE_A 
where column_b=1;

+--------+
| RESULT |
+--------+
|   27   |
|   60   |
|   13   |
+--------+

But, when I try to calculate all percentages for each value of the column_b, it's still not working.

I have tried to do this with a 'GROUP BY' but the error "Subquery returns more than 1 row" occured :

SELECT ROUND(
        (column_a*100) / (SELECT SUM(column_a) 
                          FROM TABLE_A 
                          GROUP BY column_b)
        ) AS RESULT 
FROM TABLE_A;

I understand why I have this error because the second SELECT returns 3 values and it's not possible to divide these successively. So, how can I do what I want please ?

Upvotes: 3

Views: 341

Answers (2)

Troncoso
Troncoso

Reputation: 2463

This should do what you want. If you join on a subquery that groups by column b, you should be able to get the total to use for each record in the main table.

SELECT ROUND(TABLE_A.COLUMN_A / t.total * 100) AS RESULT
FROM TABLE_A
INNER JOIN
(
    SELECT ID, SUM(COLUMN_A) AS total, COLUMN_B
    FROM TABLE_A
    GROUP BY COLUMN_B
) AS t ON t.ID = TABLE_A.ID
WHERE t.COLUMN_B = TABLE_A.COLUMN_B

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can aggregate in a subquery and join it with the main table like this:

select t1.id, t1.column_b, t1.column_a, 100 * t1.column_a / t2.column_a perc
from table_a t1
inner join (
    select column_b, sum(column_a) column_a
    from table_a
    group by column_b
) t2 on t1.column_b = t2.column_b;

Upvotes: 2

Related Questions