How to solve this complex sql query

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to solve this complex sql query

Author

Topic

rushikeshkale
Starting Member

5 Posts

Posted - 2007-10-25 : 23:19:40

Hi Guys, I tried a lot to solve following query using having clause, Not Exist clause but failed. Please help me to solve this.

Q.Consider the following schema:
BROKER(ID integer primary key, NAME string)
ACCOUNT(ACCOUNT_NUM integer primay key,BROKER_ID references BROKER.ID,GAIN real)

Write a SQL statement to fire all brokers who lost money in at least 40% of their accounts.
Assume that every broker has at least one account. Firing means that the rows corresponding to
that broker must be removed from the Broker table and BrokerId in corresponding rows in
Account table must be set to null.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 23:25:25

What have you tried so far ?

KH
[spoiler]Time is always against us[/spoiler]

rushikeshkale
Starting Member

5 Posts

Posted - 2007-10-25 : 23:50:18

I tried write query as follows:
Delete FROM BROKER B,ACCOUNT A WHERE B.ID=A.BROKER_ID
AND EXIST(
Select BROKER_ID FROM ACCOUNT A1 GROUP BY BROKER_ID
HAVING 0.4 > (
(
SELECT COUNT(*) FROM ACCOUNT A2 WHERE A1.BROKER_ID = A2.BROKER_ID
)
EXCEPT
( SELECT COUNT(*)FROM ACCOUNT A2 WHERE A1.BROKER_ID = A2.BROKER_ID AND GAIN < 0 )
)
)

But i think this approch is wrong, I am confused about how to check 40% losses

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 00:05:17

First of all you need to know how to check for the percentage of loss a/c

It should be :

percentage of loss a/c = (no of loss a/c) * 100.0 / (total no of a/c)

right ?

Don't go straight into delete statement.
Try to write query to achieve the following in sequence
(1). Find the broker and the with loss (gain < 0) a/c.
(2). Now add a condition to (1). Find the broker and the with loss (gain < 0) a/c AND the percentage of loss a/c
(3). Add another condition, the percentage of loss a/c > 40%
(4). Now you should be able to do the UPDATE query

KH
[spoiler]Time is always against us[/spoiler]

rushikeshkale
Starting Member

5 Posts

Posted - 2007-10-26 : 00:54:02

Thanks for this Great Precise Help!

I wrote a following query as per you instruction and combined it as follows.

DELETE FROM BROKER WHERE ID = ( SELECT A1.BROKER_ID FROM ACCOUNT A1 WHERE A1.GAIN < 0
AND 0.4 < (SELECT COUNT(*) FROM ACCOUNT A2
WHERE A2.GAIN<0)*100/(SELECT COUNT(*) FROM ACCOUNT A3)
);

Please check this and let me know whether I got the JackPOT or Not!

rushikeshkale
Starting Member

5 Posts

Posted - 2007-10-26 : 01:38:45

Hi Khtan,

I ran above query and got following error:

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

what went wrong here?

rushikeshkale
Starting Member

5 Posts

Posted - 2007-10-26 : 01:49:24

Finallly I got the Solution!, Thanks for this Great help!

DELETE FROM BROKER
WHERE BROKER.ID = (SELECT A1.BROKER_ID FROM ACCOUNT A1
WHERE A1.GAIN < 0.0
GROUP BY A1.BROKER_ID
HAVING 0.4 < ((SELECT COUNT(*)
FROM ACCOUNT A2
WHERE A1.BROKER_ID = A2.BROKER_ID AND A2.GAIN < 0.0)*100/(SELECT COUNT(*)FROM ACCOUNT A3 WHERE A1.BROKER_ID=A3.BROKER_ID)));

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 01:52:33

quote:
Originally posted by rushikeshkale

Hi Khtan,

I ran above query and got following error:

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

what went wrong here?

Looks like you are using Oracle database. FYI this is a Microsoft SQL Server forum.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 02:04:59

You can also try to re-write your query using INNER JOIN

This is how you find the number of loss a/c for each broker


           SELECT BROKER_ID, cnt = COUNT(*)
           FROM   ACCOUNT 
           WHERE  GAIN    < 0.0
           GROUP BY BROKER_ID

Finding all account per broker is easy. Just remove the WHERE clause


           SELECT BROKER_ID, cnt = COUNT(*)
           FROM   ACCOUNT 
           GROUP BY BROKER_ID

Now to combine all these

SELECT b.ID, b.NAME, total_acc = a.cnt, loss_ac = l.cnt
FROM   BROKER b
INNER JOIN
      (
           SELECT BROKER_ID, cnt = COUNT(*)
           FROM   ACCOUNT 
           WHERE  GAIN    < 0.0
           GROUP BY BROKER_ID
      ) l ON b.ID = l.BROKER_ID
INNER JOIN
      (
           SELECT BROKER_ID, cnt = COUNT(*)
           FROM   ACCOUNT 
           GROUP BY BROKER_ID       
      ) a ON b.ID = a.BROKER_ID

this will give u a lists of broker with total no of a/c and no of loss a/c.

You should be able to proceed with the rest.

Note : The query is intended for MS SQL Server. It should be able to run on Oracle also.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources