By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,384 Members | 2,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,384 IT Pros & Developers. It's quick & easy.

Query using the Having clause

P: 7
Hi guys,

I have some trouble with the following query.
Please look into this and lemme know the solution ASAP.
It certainly involves aggregations and the 'having clause'

BROKER(
ID integer primary key
, NAME string)

ACCOUNT(
ACCOUNT_NUM integer primay key,
BROKER_ID references BROKER.ID,
GAIN real)

The value of the attribute GAIN in the relation ACCOUNT indicates whether money has been lost or gained in that account. If value of GAIN is less than zero than the account has lost money. Otherwise the account is either even (if value of GAIN is zero or NULL) or has gained money (if value of GAIN is greater than zero).

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.
Oct 26 '07 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,396
There's no way you can do all that in one SQL statement. At best you're looking at two statements. Two update queries with subqueries.

What have you tried so far?
Oct 26 '07 #2

P: 7
There's no way you can do all that in one SQL statement. At best you're looking at two statements. Two update queries with subqueries.

What have you tried so far?
i have first grouped by the broker id and in the having clause i want to count all the rows for a particular rating, which is easy and want to compare that with all the rows which have a negative gain -- which i am not able to do!
jus see this!
Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having (0.4*count(ac.broker_id) in (select ac.broker_id, count(ac.broker_id) from account ac group by ac.broker_id)< count(ac1.broker_id) in (select ac1.broker_id, count(ac1.broker_id) from account ac1 where ac1.gain<0 group by ac1.broker_id));
  2.  
Oct 26 '07 #3

Rabbit
Expert Mod 10K+
P: 12,396
i have first grouped by the broker id and in the having clause i want to count all the rows for a particular rating, which is easy and want to compare that with all the rows which have a negative gain -- which i am not able to do!
jus see this!
Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having (0.4*count(ac.broker_id) in (select ac.broker_id, count(ac.broker_id) from account ac group by ac.broker_id)< count(ac1.broker_id) in (select ac1.broker_id, count(ac1.broker_id) from account ac1 where ac1.gain<0 group by ac1.broker_id));
  2.  
Please use code tags as per the forum guidelines.

As for your SQL. I can not make heads or tails of it. You have subquery in subquery using incorrect syntax. You've made it too complex. You only need one subquery. You just need to check that the count of negative accounts divided by a subquery returning a total count for that broker doesn't surpass your threshold. You'll also need to group by the Broker ID and only include fields where the gain is negative.
Oct 26 '07 #4

P: 7
Please use code tags as per the forum guidelines.

As for your SQL. I can not make heads or tails of it. You have subquery in subquery using incorrect syntax. You've made it too complex. You only need one subquery. You just need to check that the count of negative accounts divided by a subquery returning a total count for that broker doesn't surpass your threshold. You'll also need to group by the Broker ID and only include fields where the gain is negative.

I was trying different ways of getting it done. I know it needs only one sub-query
this is the one I first framed and I guess it will make sense.

Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having (0.4*count(*) < (select count(ac1.broker_id) from account ac1 where ac1.gain<0 ));
Instead of the division operator I have used it this way!
Oct 26 '07 #5

Rabbit
Expert Mod 10K+
P: 12,396
I was trying different ways of getting it done. I know it needs only one sub-query
this is the one I first framed and I guess it will make sense.

Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having (0.4*count(*) < (select count(ac1.broker_id) from account ac1 where ac1.gain<0 ));
Instead of the division operator I have used it this way!
You're close. I see where you're getting at but:

1) You set up your alias incorrectly and in the wrong place. An alias is set up with the AS keyword in between. So Account AS ac1. Also, it shouldn't be in the subquery but in the outer query.

2) Your subquery needs to account for the BrokerID of the outer query.
Oct 26 '07 #6

P: 7
You're close. I see where you're getting at but:

1) You set up your alias incorrectly and in the wrong place. An alias is set up with the AS keyword in between. So Account AS ac1. Also, it shouldn't be in the subquery but in the outer query.

2) Your subquery needs to account for the BrokerID of the outer query.
It did not work when I used the 'AS' keyword. May be it is different in ORACLE.
and what do you mean by the 'needs to account for the BrokerID of the outer query'?
Oct 26 '07 #7

P: 7
It did not work when I used the 'AS' keyword. May be it is different in ORACLE.
and what do you mean by the 'needs to account for the BrokerID of the outer query'?
Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having 0.4*count(*) < (select count(ac1.broker_id) from account ac1 where ac1.broker_id=broker_id and ac1.gain<0);
even this is not working!
Oct 26 '07 #8

Rabbit
Expert Mod 10K+
P: 12,396
Expand|Select|Wrap|Line Numbers
  1. select broker_id from account group by broker_id having 0.4*count(*) < (select count(ac1.broker_id) from account ac1 where ac1.broker_id=broker_id and ac1.gain<0);
even this is not working!
Please use Code tags as per the forum guidelines, second warning.

You're using Oracle? You do realize that this is the Access forum and that we have an Oracle forum right?

It's been a really long time since I've worked with Oracle. But, assuming you've set up the alias correctly, if you remember what I said in the previous post, it's still in the wrong place. The alias has to be for the outer query.
Oct 26 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.