SQL Server Queries to sum two columns as row in the same table

uttx8gqw  于 2023-11-16  发布在  其他
关注(0)|答案(2)|浏览(107)

I have two tables Customers and Operations ; I want to sum the data Withdraw and Deposit from operations table along with customer name from customer table.

I have created a stored procedure:

select 
    customer_id, customer_fullname, 
    amount_withdraw, amount_deposit, entry_date 
from 
    Operations b 
join 
    Customers a on b.customer_id2 = a.customer_id
where  
    customer_id = 19

This is the result I got:

But the result I want should look like this

Do I need two procedures or what?

sql-server

2条答案

按热度按时间
nue99wik

nue99wik1#

You can do this with UNION so effectively join multiple queries into the same resultset:

http://sqlfiddle.com/#!18/6f74f/2

select customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
from Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
where customer_id = 19

UNION

SELECT customer_id ,customer_fullname , SUM(amount_withdraw), SUM(amount_deposit) ,null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

UNION 

SELECT customer_id ,customer_fullname , SUM(amount_deposit) - SUM(amount_withdraw) ,null, null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

Is it a good idea to do this... Not usually. Formatting and aggregates like this are better left to the application or reporting layer. The first problem with the above query is the order of the results, the second problem is that you are using the same column to return different sets of information.

If you really want to do this in SQL, we can fix the order based on the nullability of the date column, but the syntax looks a bit strange. We can't use functions in the order by clause for UNION queries, so to do this we have to wrap the rollup data into either a sub-query or a CTE

http://sqlfiddle.com/#!18/6f74f/1

SELECT * FROM (
  select customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
from Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
where customer_id = 19

UNION

SELECT customer_id ,customer_fullname , SUM(amount_withdraw), SUM(amount_deposit) ,null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

UNION 

SELECT customer_id ,customer_fullname , SUM(amount_deposit) - SUM(amount_withdraw) ,null, null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname
) RollupData
ORDER BY ISNULL(entry_date,'2099-12-31'), ISNULL(amount_deposit, 999999999)

The sorting can be fixed a different way by adding an arbitrary column instead of relying on nullability of the entry_date makes it a bit more obvious, we can then remove the column from the final output if you are using the result directly, but often we would leave it in so that the sorting can be re-affirmed in the reporting layer. This shows how to remove it:

Another term for this order column is a Discriminator as it allows us to identify which of the underlying queries the results came from.

http://sqlfiddle.com/#!18/7022a/7

SELECT customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
FROM
(
    select 1 as [order], customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
    from Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    where customer_id = 19

    UNION

    SELECT 2 as [order], customer_id ,customer_fullname , SUM(amount_withdraw), SUM(amount_deposit) ,null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname

    UNION 

    SELECT 3 as [order], customer_id ,customer_fullname , SUM(amount_deposit) - SUM(amount_withdraw) ,null, null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname
  
) RollupData
ORDER BY [order]
customer_idcustomer_fullnameamount_withdrawamount_depositentry_date
19Ali Ahmed Omar5600002023-01-02
19Ali Ahmed Omar6400002023-04-08
19Ali Ahmed Omar3200002023-06-02
19Ali Ahmed Omar0650002023-09-04
19Ali Ahmed Omar0782002023-10-25
19Ali Ahmed Omar0320002023-11-05
19Ali Ahmed Omar152000175200(null)
19Ali Ahmed Omar23200(null)(null)

If you wanted to package this into a stored procedure, then use this syntax:

http://sqlfiddle.com/#!18/8a727/1

CREATE PROCEDURE CustomerOperations (
    @customerId INT
  )
  AS
  BEGIN
  
  SELECT customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
FROM
(
    select 1 as [order], customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
    from Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    where customer_id = @customerId

    UNION

    SELECT 2 as [order], customer_id ,customer_fullname , SUM(amount_withdraw), SUM(amount_deposit) ,null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = @customerId
    GROUP BY customer_id ,customer_fullname

    UNION 

    SELECT 3 as [order], customer_id ,customer_fullname , SUM(amount_deposit) - SUM(amount_withdraw) ,null, null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = @customerId
    GROUP BY customer_id ,customer_fullname
  
) RollupData
ORDER BY [order], entry_date

END

A more standard solution would be to move the aggregates to other columns, you could also use window queries, but those would have a different layout to your request.

A simple window query solution:

http://sqlfiddle.com/#!18/6f74f/4

select customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
     , SUM(amount_withdraw) OVER (PARTITION BY customer_id) as Total_withdraw
     , SUM(amount_deposit) OVER (PARTITION BY customer_id) as Total_deposit
     , SUM(amount_deposit - amount_withdraw) OVER (PARTITION BY customer_id) as Overall_Balance
from Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
where customer_id = 19
ORDER BY customer_id, entry_date
customer_idcustomer_fullnameamount_withdrawamount_deposit entry_dateTotal_withdrawTotal_depositOverall_Balance
19Ali Ahmed Omar5600002023-01-02152000175200
19Ali Ahmed Omar6400002023-04-08152000175200
19Ali Ahmed Omar3200002023-06-02152000175200
19Ali Ahmed Omar0650002023-09-04152000175200
19Ali Ahmed Omar0782002023-10-25152000175200
19Ali Ahmed Omar0320002023-11-05152000175200

You can still use UNION to produce a similar result, however when we do this, it is really usefull to keep the discriminator column so we know what each row represents:

http://sqlfiddle.com/#!18/11d586/4

SELECT [Order] as LineType, customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date , Total_withdraw, Total_deposit, Overall_Balance 
FROM
(
    select 1 as [order], customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date, null as Total_withdraw, null as Total_deposit, null as Overall_Balance
    from Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    where customer_id = 19

    UNION

    SELECT 2 as [order], customer_id ,customer_fullname , null, null, null, SUM(amount_withdraw), SUM(amount_deposit) ,null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname

    UNION 

    SELECT 3 as [order], customer_id ,customer_fullname , null, null, null, null, null, SUM(amount_deposit) - SUM(amount_withdraw) 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname
  
) RollupData
ORDER BY [order], [entry_date]
LineTypecustomer_idcustomer_fullnameamount_withdrawamount_depositentry_dateTotal_withdrawTotal_depositOverall_Balance
119Ali Ahmed Omar5600002023-01-02(null)(null)(null)
119Ali Ahmed Omar3700002023-03-02(null)(null)(null)
119Ali Ahmed Omar6400002023-04-08(null)(null)(null)
119Ali Ahmed Omar3200002023-06-02(null)(null)(null)
119Ali Ahmed Omar0650002023-09-04(null)(null)(null)
119Ali Ahmed Omar0782002023-10-25(null)(null)(null)
119Ali Ahmed Omar0320002023-11-05(null)(null)(null)
219Ali Ahmed Omar(null)(null)(null)189000175200(null)
319Ali Ahmed Omar(null)(null)(null)(null)(null)-13800

Finally, we need to talk abot efficiency. Given that your aggregate rows apply to the original query, and you are using stored procedures, we can use temporary tables to store the results of the initial query and then we can query against that recordset directly rather than back into the database.

  • This is can be thought of as similar to how Window Query functions work

https://dbfiddle.uk/URG52cH1 (Dot Net Fiddle stopped working ;)

CREATE PROCEDURE CustomerOperations (
    @customerId INT
)
AS
BEGIN
  
  -- Select the record set into a temporary table
  SELECT customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
  INTO #CustomerOperationsTemp
  FROM Operations b 
  INNER JOIN Customers a on b.customer_id2 = a.customer_id
  WHERE customer_id = @customerId
    
  SELECT customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
  FROM
  (
    SELECT 1 as [order], customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
    FROM #CustomerOperationsTemp

    UNION

    SELECT 2 as [order], customer_id ,customer_fullname , SUM(amount_withdraw), SUM(amount_deposit) ,null 
    FROM #CustomerOperationsTemp
    GROUP BY customer_id ,customer_fullname

    UNION 

    SELECT 3 as [order], customer_id ,customer_fullname , SUM(amount_deposit) - SUM(amount_withdraw) ,null, null 
    FROM #CustomerOperationsTemp
    GROUP BY customer_id ,customer_fullname 
  ) RollupData
  ORDER BY [order], entry_date

END

This is effectively the same workflow that a reporting or application layer would typically use:

  1. Query the data
  2. Apply Aggregates
  3. Format the data
赞(0）分享  回复(0）举报  2023-11-16
rxztt3cl

rxztt3cl2#

Use SUM and Group By

select customer_id ,customer_fullname
,SUM(b.amount_withdraw) as TotalWithdraw
,SUM(b.aount_deposit) as TotalDeposit
,SUM(b.aount_deposit) - SUM(b.amount_withdraw) as Balance

from Operations b join Customers a
on b.customer_id2 = a.customer_id
GROUP BY customer_id, customer_fullname
where customer_id = 19

You might want to make the sp accept a parameter for customer id

赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com