CODEWITHSUNDEEP
sqloracle-databasegroup-byhavingora-00979
zoro
zoro

Reputation: 17

sql issue with having clause

Im trying to find query each model with their most used ram configuration.

Table:

PC (code, model, speed, ram, hd, cd, price)

So far, I was able to list every model with every ram config and number of times the ram config has been used.

select model, ram, max(config)
  from (select model,ram,count(ram) as config
          from pc 
         group by model, ram)
 group by model, ram

Output:

MODEL   RAM  MAX(CONFIG)
------- ---- -----------
1232    64   2
1232    32   2
1233    128  3
1121    128  3
1233    64   1
1260    32   1

I face problems when I try to have model listed with its most used ram.

select model, ram
  from (select model, ram, count(ram) as config
          from pc 
         group by model, ram)
 group by model
having config = max(config);


Error : ORA-00979: not a GROUP BY expression

Upvotes: 0

Views: 72

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

with x as 
(select model,ram,count(ram) as config
from pc 
group by model,ram)
, y as 
(select model, max(config) as mxconfig from x group by model)
select x.model, x.ram --choose max(x.ram) or min(x.ram) in case of a tie and group by x.model
from x join y on x.model = y.model and x.config = y.mxconfig

This solution uses cte to achieve what you need. If you need to get either max or min ram when there is a tie for config, you should have one more group by on model.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

One approach is to use window functions:

select model, ram
from (select model, ram,
             row_number() over (partition by model order by count(*) desc) as seqnum
      from pc 
      group by model, ram
     ) mr
where seqnum = 1;

Upvotes: 0

Patrick Murphy
Patrick Murphy

Reputation: 2329

I think what you are looking for is:

SELECT model,ram FROM (SELECT model,ram,count(ram) AS config
FROM pc 
GROUP BY model,ram)
WHERE config=max(config)

The records should already be grouped by your sub query

Upvotes: 0

Related Questions