Display data in SQL server

Question

0.00/5 (No votes)

See more:

Could you help me with this situation? I have two tables and each table has columns also, one table and one column have steady.

One table is
Name Money
Khalid 5000

Two Table is
No Name Money
1 Khalid 4000
2 Mohammed 6000
3 Khalid 10000
4 Khalid 10000

I want to get data like this

No Name Money TotalMoney
0 Khalid 5000 5000
1 Khalid 4000 9000
3 Khalid 10000 19000
4 Khalid 10000 29000

What I have tried:

SELECT No, D, VN, RT, PD, RD, CAST(NT AS nvarchar(MAX)) AS NT
FROM
(SELECT ISNULL(NULL, 0) AS No, ISNULL(NULL, '') AS D, ISNULL(NULL, '') AS VN, ISNULL(OD, 0) AS RT, ISNULL(NULL, 0) AS PD, ISNULL(OD, 0) AS RD, ISNULL(NULL, 'Old Debt') AS NT FROM TBL_S WHERE N='Mohammed'
UNION
SELECT No, D, ISNULL(NULL, '') AS VN, RT, ISNULL(NULL, 0) AS PD, ((SELECT (S.OD + (SELECT SUM(RT) FROM TBL_I_I IMIM WHERE IMIM.No <= M.No)) AS RT FROM TBL_I_I M INNER JOIN TBL_S AS S ON S.N = M.N WHERE M.N='Mohammed') + (SELECT ISNULL(S.OD, 0) FROM TBL_S AS S WHERE S.N=IM.N)) AS RD, CAST(NT AS nvarchar(MAX)) FROM TBL_I_I AS IM WHERE IM.N ='Mohammed')
resutls ORDER BY D ASC

It give me error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Posted 8-Aug-22 6:39am

Karam Ibrahim

Updated 9-Aug-22 2:16am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2022-08-09T02:16:00

How about:

SQL

WITH cteSource As
(
    SELECT
        0 As No,
        Name,
        Money
    FROM
        TableOne
    
    UNION ALL
    
    SELECT
        No,
        Name,
        Money
    FROM
        TableTwo As T2
    WHERE
        Exists
        (
            SELECT 1
            FROM TableOne As T1
            WHERE T1.Name = T2.Name
        )
)
SELECT
    No,
    Name,
    Money,
    SUM(Money) OVER (PARTITION BY Name ORDER BY No ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As TotalMoney
FROM
    cteSource
;

Output:

| No | Name   | Money  | TotalMoney |
|----|--------|--------|------------|
| 0  | Khalid | 5000   | 5000       |
| 1  | Khalid | 4000   | 9000       |
| 3  | Khalid | 10000  | 19000      |
| 4  | Khalid | 10000  | 29000      |

OVER Clause (Transact-SQL) - SQL Server | Microsoft Docs[^]