Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
nitagar3107
Starting Member
1 Post |
Posted - 2012-11-19 : 04:13:59
|
Hi,I am trying to execute a sql query which says to count the number of customer who live in same city of employees and also find their average checking balance. I have four tables customer(cid,city ,cname)..employees(eid,city,ename)....transactions(tid,aid,cid,eid,trans_date,amount,trans_type)... and accounts(aid,balance,account_type,date_opened).. Please anyone can help me out it will be great... Thanks.. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-19 : 13:11:54
|
Though your question is not clear:Here is my shot:Select e.city,Count(Cid),AVG(balance) as CheckingBalancefrom customer cinner join transactions t on t.cid = c.cidinner join employees e on e.eid = t.eidinner join accounts a on a.tid = t.tidWhere account_type = 'Checking'Group by e.city |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-20 : 02:11:34
|
[code]SELECT e.*,t.CustCnt,t.AvgBalFROM employees eINNER JOIN (SELECT c.city,COUNT(DISTINCT c.cid) AS CustCnt, AVG(a.balance*1.0) AS AvgBal FROM customer c INNER JOIN transactions t ON t.cid = c.cid INNER JOIN accounts a ON a.aid = t.aid WHERE a.account_type='Checking' GROUP BY c.city )tOn t.city = e.city[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|