?
Solved

Microsoft SQL Queries

Posted on 2016-11-02
11
Medium Priority
?
98 Views
Last Modified: 2016-11-04
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
0
Comment
Question by:Bob Tian
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 37

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41870912
can you provide some sample data for below -

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.
1
 

Author Comment

by:Bob Tian
ID: 41870935
For example, if the average transaction amount of all business accounts is $2,000 then return transactions from business accounts where the average transaction amount for that account is greater than $6,000
0
 
LVL 9

Accepted Solution

by:
Mike in IT earned 1000 total points
ID: 41870967
Most of the tables that you show you don't need for the question that you asked. you will only need the Account, Branch, and Transaction tables. And if you don't care about the name of the Branch you don't even need the Branch table. Without sample data I can't give you specifics, but a god starting point would be this:

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(TransactionAmount{all})/count(transNumber){all})
Group by a.type

Open in new window


This is just a bare bones attempt since I don't have concrete data to test on. Also since you are looking to compare based on transaction amount, you need to have that data available in the transaction table or you won't be able to compare on it.
1
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 41871084
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.

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.transNumber

Open in new window

'm not quite sure what this means "and amount of transactions of accounts " i guess it is a count of transations and that is what Michael has used.

no points please
0
 
LVL 37

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871335
I think the filter should be in the Having Clause, I think aggregates in where clause with group by will give the syntax error. Try..

--

SELECT
      b.branchName         [Branch Name]
    , a.type               [Account Type]
    , a.accNumber          [Account]
    , T.transNumber        [Transaction Number]
    , COUNT(T.transNumber) [# of Transactions]
FROM Account a
INNER JOIN Branch b ON a.branchNumber = b.branchNumber
INNER JOIN Transactions T ON T.accNumber = a.accNumber
GROUP BY
      b.branchName
    , a.type
    , a.accNumber
    , T.transNumber
HAVING (  SUM(t.TransactionAmount) * 1. / COUNT(T.transNumber) ) > ( 3. * SUM(T.TransactionAmount) / COUNT(T.transNumber) )

--

Open in new window

2
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41871566
oops,
Yes, filtering on aggregated values must be in the having clause
can't believe I missed that
0
 
LVL 9

Assisted Solution

by:Mike in IT
Mike in IT earned 1000 total points
ID: 41871943
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. I did say that my earlier response was just a starting point, the asker will have to do their own testing to find out what will work for them. It's difficult to give good advice when there is no data to test against.
1
 
LVL 37

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871946
Agreed that sometimes when we just post queries without the data we may miss on the syntax.

:)

Regards,
Pawan
1
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41872063
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.
1
 
LVL 37

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41873442
Hi Bob Tian,
Any feedback on this?

Regards,
Pawan
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question