Solved

Microsoft SQL Queries

Posted on 2016-11-02
11
56 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
11 Comments
 
LVL 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 6

Accepted Solution

by:
Mike in IT earned 250 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
 
LVL 48

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 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 48

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 6

Assisted Solution

by:Mike in IT
Mike in IT earned 250 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 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 48

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 24

Assisted Solution

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

Regards,
Pawan
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now