AliDK AliDK - 6 months ago 35
SQL Question

how to solve Subquery returns more than 1 row error in multiple selection

I have a very long MYSQL query which select a total number from four tables, but one of subqueries return more than one row and cause an error.

this is my query:

select created,
(select coalesce(sum(equal_to_dollar), 0) from capitals group by created ) as capital,

(select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from transactions where currency = 'دالر') +
(select coalesce(sum(equal_to_dollar), 0) from transactions where incoming != 0 ) -
(select coalesce(sum(equal_to_dollar), 0) from transactions where outgoing != 0 ) as total_transaction,

(select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from temporary_clients where currency = 'دالر') +
(select coalesce(sum(equal_to_dollar), 0) from temporary_clients where incoming != 0 ) -
(select coalesce(sum(equal_to_dollar), 0) from temporary_clients where outgoing != 0 ) as total_temp_client,

(select coalesce(sum(outgoing), 0) - coalesce(sum(incoming), 0) from money_transmission where currency = 'دالر') +
(select coalesce(sum(equal_to_dollar), 0) from money_transmission where outgoing != 0 ) -
(select coalesce(sum(equal_to_dollar), 0) from money_transmission where incoming != 0 ) as total_transmission,

(select coalesce(total_transaction + total_temp_client + total_transmission, 0)) as total,

(select capital - abs(coalesce(total_transaction + total_temp_client + total_transmission, 0))) as result
from capitals group by created


in this query the first selection
created
returns more than one value and the first subquery which end with
as capital
also return more than one value.

this query should return a result of calculation of capital with total of other three tables for each date in database.

I check many questions that has the same error and all of them used
IN
or
inner join
in
where
clause but i think my case is different and I don't know what to do.

please help me if you have any idea how can I solve this problem.

thank you :)

Answer

Try something like:

select created,c.sum_dollars

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from transactions where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from transactions where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from transactions where outgoing != 0 ) as total_transaction,

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from temporary_clients where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where outgoing != 0 ) as total_temp_client,

    (select coalesce(sum(outgoing), 0) - coalesce(sum(incoming), 0) from money_transmission where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where outgoing != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where incoming != 0 ) as total_transmission,

    (select coalesce(total_transaction + total_temp_client + total_transmission, 0)) as total,

    (select capital - abs(coalesce(total_transaction + total_temp_client + total_transmission, 0))) as result
    from capitals
 JOIN (select coalesce(sum(equal_to_dollar), 0) as sum_dollars, created from capitals group by   created )  c ON capitals.created = c.created
 group by created

I can't really test cause I don't know you database schema. You should JOIN those result sets anyway.