Query using the Having clause

[rbukkara]

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.

[Rabbit]

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?

[rbukkara]

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!

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));
 

[Rabbit]

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!

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));
 

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.

[rbukkara]

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.

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!

[Rabbit]

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.

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.

[rbukkara]

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'?

[rbukkara]

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'?

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!

[Rabbit]

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.