Bob Tian
asked on
Microsoft SQL Queries
Hello, I am having trouble with writing some queries,
I want to find the branch name, account type, account number, transaction number and amount of transactions of accounts where the average transaction amount is greater than three times the (overall) average transaction amount of accounts of that type.
The table:
Customer = {customerID, firstName, lastName, income, birthDate }
Account = {accNumber, type, balance, branchNumber(FK-Branch)}
Owns = {customerID(FK-Customer), accNumber(FK-Account)}
Transactions = {transNumber, accNumber(FK-Account), amount}
Employee = {sin, firstName, lastName, salary, branchNumber(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN(FK-Employee), budget}
I am unsure how to start this query. Any help would be appreciated
I want to find the branch name, account type, account number, transaction number and amount of transactions of accounts where the average transaction amount is greater than three times the (overall) average transaction amount of accounts of that type.
The table:
Customer = {customerID, firstName, lastName, income, birthDate }
Account = {accNumber, type, balance, branchNumber(FK-Branch)}
Owns = {customerID(FK-Customer), accNumber(FK-Account)}
Transactions = {transNumber, accNumber(FK-Account), amount}
Employee = {sin, firstName, lastName, salary, branchNumber(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN(FK-Employee), budget}
I am unsure how to start this query. Any help would be appreciated
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Only in MySQL that I know of can you GROUP BY just one column, but have many more non-aggregating column in the select clause.
no points please
SELECT
b.branchName [Branch Name]
, a.type [Account Type]
, a.accNumber [Account]
, T.transNumber [Transaction Number]
, COUNT(T.transNumber) [# of Transactions]
FROM Account a
JOIN Branch b ON a.branchNumber = b.branchNumber
JOIN Transactions T ON T.accNumber = a.accNumber
WHERE (SUM(t.TransactionAmount) / COUNT(T.transNumber)) > (3 * SUM(T.TransactionAmount) / COUNT(T.transNumber))
GROUP BY
b.branchName
, a.type
, a.accNumber
, T.transNumberno points please
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops,
Yes, filtering on aggregated values must be in the having clause
can't believe I missed that
Yes, filtering on aggregated values must be in the having clause
can't believe I missed that
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, putting the filter in the 'having' clause would certainly be better than using a 'group by'. Though I think that using either one would work.
To get the aggregations you need the group by.
To filter by aggregated values must be after the group by in the having clause.
They are rules of sql syntax, not options.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER