Queries to sum two columns as row in the same table
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?
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_id | customer_fullname | amount_withdraw | amount_deposit | entry_date |
|---|---|---|---|---|
| 19 | Ali Ahmed Omar | 56000 | 0 | 2023-01-02 |
| 19 | Ali Ahmed Omar | 64000 | 0 | 2023-04-08 |
| 19 | Ali Ahmed Omar | 32000 | 0 | 2023-06-02 |
| 19 | Ali Ahmed Omar | 0 | 65000 | 2023-09-04 |
| 19 | Ali Ahmed Omar | 0 | 78200 | 2023-10-25 |
| 19 | Ali Ahmed Omar | 0 | 32000 | 2023-11-05 |
| 19 | Ali Ahmed Omar | 152000 | 175200 | (null) |
| 19 | Ali Ahmed Omar | 23200 | (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_id | customer_fullname | amount_withdraw | amount_deposit entry_date | Total_withdraw | Total_deposit | Overall_Balance |
|---|---|---|---|---|---|---|
| 19 | Ali Ahmed Omar | 56000 | 0 | 2023-01-02 | 152000 | 175200 |
| 19 | Ali Ahmed Omar | 64000 | 0 | 2023-04-08 | 152000 | 175200 |
| 19 | Ali Ahmed Omar | 32000 | 0 | 2023-06-02 | 152000 | 175200 |
| 19 | Ali Ahmed Omar | 0 | 65000 | 2023-09-04 | 152000 | 175200 |
| 19 | Ali Ahmed Omar | 0 | 78200 | 2023-10-25 | 152000 | 175200 |
| 19 | Ali Ahmed Omar | 0 | 32000 | 2023-11-05 | 152000 | 175200 |
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]
| LineType | customer_id | customer_fullname | amount_withdraw | amount_deposit | entry_date | Total_withdraw | Total_deposit | Overall_Balance |
|---|---|---|---|---|---|---|---|---|
| 1 | 19 | Ali Ahmed Omar | 56000 | 0 | 2023-01-02 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 37000 | 0 | 2023-03-02 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 64000 | 0 | 2023-04-08 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 32000 | 0 | 2023-06-02 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 0 | 65000 | 2023-09-04 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 0 | 78200 | 2023-10-25 | (null) | (null) | (null) |
| 1 | 19 | Ali Ahmed Omar | 0 | 32000 | 2023-11-05 | (null) | (null) | (null) |
| 2 | 19 | Ali Ahmed Omar | (null) | (null) | (null) | 189000 | 175200 | (null) |
| 3 | 19 | Ali 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:
- Query the data
- Apply Aggregates
- Format the data
- An arithmetic expression with a DATETIME value is invalid while using PreparedStatement with Arithmetic calculation in sql later
- space is not allowed after parameter prefix ':' in SpEL support in Spring Data JPA @Query definitions
- SQLAlchemy - Get query results in same order as IN clause
- SQL Grouping and Nested Concatenation
- Extract weekday, millisecond, microsecond, nanosecond from a date in Postgres
- How can I refer to a variable in postgresql dynamic SQL?
- How to pull width & height out of string?
- Add new columns without using Alter
- Get columns of index on DB2
- Athena Query - "NOT IN" slow performance
- SQL one to one relationship vs. single table
- How can I number the instance of a duplicate record?
- How to fix SQL for AWS Athena
- SQL SELECT query not working in mysql
- How can i insert/ select into a table and delete copied records from original table?
- How to convert oracle regex pattern into Databricks compatible version?
- Select min or max based on where duplicate value exists
- Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.' Error with SQL Pivot
- WHERE clause pulling incorrect values
- Sql select all conversation by group
- Create an inline SQL table on the fly (for an excluding left join)
- How to execute raw SQL in Flask-SQLAlchemy app
- Update SQL table using selection from another table
- Clarification on joins
- Foreign key constraint may cause cycles or multiple cascade paths?
- PowerApps retrieving data from SQL using compound criteria
- T-SQL Get File Extension Name from a Column
- postgres - How to parse json array elements?
- How to query the distribution (grouped by value range) of a column value in Oracle?
- How to join two tables where the entries are listed as rows in one table and as columns in the other table?