Write Q𝙪𝙚𝙧𝙮 𝙩𝙤 𝙛𝙞𝙣𝙙 𝙩𝙝𝙚 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙩𝙧𝙖𝙣𝙨𝙖𝙘𝙩𝙞𝙤𝙣 𝙖𝙢𝙤𝙪𝙣𝙩 𝙛𝙤𝙧 𝙚𝙖𝙘𝙝 𝙖𝙘𝙘𝙤𝙪𝙣𝙩.

[Pankaj-Str]

𝙒𝙧𝙞𝙩𝙚 𝙖 𝙎𝙌𝙇 𝙦𝙪𝙚𝙧𝙮 𝙩𝙤 𝙛𝙞𝙣𝙙 𝙩𝙝𝙚 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙩𝙧𝙖𝙣𝙨𝙖𝙘𝙩𝙞𝙤𝙣 𝙖𝙢𝙤𝙪𝙣𝙩 𝙛𝙤𝙧 𝙚𝙖𝙘𝙝 𝙖𝙘𝙘𝙤𝙪𝙣𝙩, 𝙗𝙪𝙩 𝙤𝙣𝙡𝙮 𝙛𝙤𝙧 𝙩𝙝𝙤𝙨𝙚 𝙖𝙘𝙘𝙤𝙪𝙣𝙩𝙨 𝙬𝙝𝙚𝙧𝙚 𝙩𝙝𝙚 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙩𝙧𝙖𝙣𝙨𝙖𝙘𝙩𝙞𝙤𝙣 𝙖𝙢𝙤𝙪𝙣𝙩 𝙚𝙭𝙘𝙚𝙚𝙙𝙨 𝙩𝙝𝙚 𝙤𝙫𝙚𝙧𝙖𝙡𝙡 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙩𝙧𝙖𝙣𝙨𝙖𝙘𝙩𝙞𝙤𝙣 𝙖𝙢𝙤𝙪𝙣𝙩 𝙖𝙘𝙧𝙤𝙨𝙨 𝙖𝙡𝙡 𝙖𝙘𝙘𝙤𝙪𝙣𝙩𝙨.

𝘼𝙡𝙨𝙤, 𝙞𝙣𝙘𝙡𝙪𝙙𝙚 𝙩𝙝𝙚 𝙖𝙘𝙘𝙤𝙪𝙣𝙩 𝙣𝙖𝙢𝙚 𝙞𝙣 𝙩𝙝𝙚 𝙧𝙚𝙨𝙪𝙡𝙩 𝙨𝙚𝙩.

[AnmolDixitB13]

use mydb

create table Accounts(
name varchar(20) not null,
account_no int unique not null,
trans_amt1 float,
trans_amt2 float,
trans_amt3 float
) ;

-- trans_amt1 = amount of first transaction
-- for simplicity, let us solve/manage the question only with a maximum 3 transactions per account

alter table Accounts
add constraint chk_amt check(trans_amt1>=0 and trans_amt2>=0 and trans_amt3>=0) ;
-- ideally transaction values are non negative, so this constraint ensures that negative values 
-- won't be entered into the table Accounts

insert into Accounts(name, account_no, trans_amt1, trans_amt2, trans_amt3)
values('abc', 1111, 100, 200, 300), ('def', 2222, 51.5, 67.32, 6741.70), 
('ghi', 3333, 549, 12, 60.3),	('jkl', 4444, 10, 49.5, 103), 
('mno', 5555, 307.4, 905, 220), ('pqr', 6666, 7842.67, 10, 23) ;

select * from Accounts

alter table Accounts
add temp_avg float ;
-- adding a column to store average transaction value of an account

update Accounts
set temp_avg = (trans_amt1 + trans_amt2 + trans_amt3)/3 ;
-- to find the average transaction value of an account


select * from Accounts
where temp_avg > (select sum(temp_avg) from Accounts)/(select count(account_no) from Accounts) ;

-- 1st subquery in the previous query finds the sum of the averages transation amount of all accounts 
-- 2nd subquery in the previous query finds the total no. of accounts which are present in the table
-- we divided 1st subquery by 2nd to find the average transaction value per account in the table

select * from Accounts
where temp_avg > (select avg(temp_avg) from Accounts);
-- a simpler and more straightforward way to do the same work

/*
Alternately, the final result can be stored in a new column and the temp_avg column can be deleted, if needed. 
*/

[armandevyas2001]

--Following code is for SQL Server

use github 
CREATE TABLE Transactions(
TransactionID INT PRIMARY KEY,
AccountName VARCHAR(50),
Amount DECIMAL(10,2)
);

-- Insert some sample data into the Transactions table
INSERT INTO Transactions(TransactionID, AccountName, Amount)
VALUES
    (1, 'Account1', 100.00),
    (2, 'Account1', 150.00),
    (3, 'Account2', 200.00),
    (4, 'Account2', 250.00),
    (5, 'Account3', 300.00),
    (6, 'Account3', 350.00),
    (7, 'Account4', 400.00),
    (8, 'Account4', 450.00),
    (9, 'Account4', 400.00),
    (10, 'Account4', 450.00),
    (11, 'Account5', 200.00),
    (12, 'Account5', 250.00),
    (13, 'Account6', 300.00),
    (14, 'Account6', 350.00),
    (15, 'Account7', 400.00),
    (16, 'Account7', 450.00);

SELECT * from Transactions;

-- **Section 1**: Execute the code up to the GROUP BY clause first
-- Select the AccountName and the average transaction amount for each group
SELECT 
---Replace NULL AccountName values with 'Averageofalltransactions' for subtotal rows to label them appropriately
COALESCE(AccountName, 'Averageofalltransactions') AS AccountName,

-- Calculate the average transaction amount for each group
AVG(Amount) AS AverageTransactionAmount

-- Select data from the Transactions table
FROM Transactions

-- Group the data by all possible combinations of AccountName, including subtotal rows and a grand total row
GROUP BY CUBE(AccountName)

-- **Section 2**: Once the data is grouped, execute the remaining code together until the end

-- Filter the groups to include only those where the average transaction amount is greater than the overall average
HAVING 
AVG(Amount) > (
-- Calculate the overall average transaction amount across all accounts
SELECT AVG(Amount)
FROM Transactions
);

[RiddhiiA]

https://github.com/developology/Average_Transaction_Amount