ORA-01427: single-row subquery returns more than one row error

1
votes

I have 2 tables (USERS and ACCOUNTS) with the following data in them:

USERS

UserID     Name          Account_Number
10         John Smith    13
20         Alex Brown    14
30         Mary Wade     34

ACCOUNTS

Account number     Amount     
13                 40
34                 30
14                 30
13                 60
14                 10

I would like to know how I can write a query to return the following results:

UserID             Name           Total amount     
13                 John Smith     100     
14                 Alex Brown     40             
34                 Mary Wade      30

The query that I have tried is:

SELECT USER_ID, NAME, (SELECT SUM(AMOUNT) FROM ACCOUNTS GROUP BY ACCOUNT) AS TOTAL_AMOUNT
FROM USERS
JOIN ACCOUNTS
USING(ACCOUNT_NUMBER)
ORDER BY TOTAL_AMOUNT DESC;

When I execute this I get the following error: ORA-01427: single-row subquery returns more than one row.

Does anyone know how I might be able to modify the query so that it works as intended?

Thanks!

3
sqloracle

3 Answers

1
votes

Please try:

select 
    Account_Number, 
    Name,
    (select SUM(Amount) from ACOUNTS b where b.[Account number]=a.Account_Number) Total
from USERS a
order by Account_Number
1
votes

Maybe something like this:

SELECT
    USERS.USER_ID,
    USERS.NAME,
    SUM(ACCOUNTS.Amount) AS TotalAmout
FROM USERS
JOIN ACCOUNTS
    ON ACCOUNTS.Account_number=USERS.Account_number
GROUP BY
    USERS.USER_ID,
    USERS.NAME
ORDER BY
    TotalAmout DESC
1
votes

Remove your subquery.. It is returning more than one row..

SELECT U.USERID, U.NAME, SUM(A.AMOUNT) AS TOTAL_AMOUNT
FROM USERS U
 INNER JOIN ACCOUNT A on U.ACCOUNT_NUMBER=A.ACCOUNT_NUMBER
GROUP BY A.ACCOUNT_NUMBER,U.USERID, U.NAME
ORDER BY SUM(A.AMOUNT) DESC;

Fiddle