CODEWITHSUNDEEP
sqloracle-database
Gilbert Abdintara
Gilbert Abdintara

Reputation: 3

Having count subquery

I am trying to get some query but I'm having a problem with my having count where it says im missing an expression. Please help

    select d.kode, rpad(d.nama,75,' ') as "NAMA", lpad(count(th.fk_distributor),10,' ') as "JUMLAH"
    from mh_distributor d 
    join th_beli th on th.fk_distributor = d.kode
    having count(td.fk_produk) > select avg("JUMLAH") as "AVERAGE" from(
        select d.nama,count(th.fk_distributor) as "JUMLAH"
        from mh_distributor d
        join th_beli th on th.fk_distributor = d.kode
        group by d.nama
    )
    group by d.nama, td.fk_produk, d.kode
    order by d.kode asc;

That is my query

Upvotes: 0

Views: 72

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use window functions!

select nk.*
from (select d.kode, d.nama, count(*) as jumlah,
             avg(count(*)) over (partition by d.nama) as avg_jumlah
      from mh_distributor d join
           th_beli th
           on th.fk_distributor = d.kode
      group by d.nama, td.fk_produk, d.kode
     ) nk
where jumlah > avg_jumlah;

I left out the conversion to strings, because I think that just makes the logic harder to follow -- of course, add it back in to the outer query for your particular use-case.

This should have much better performance than a complicated having clause.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Just wrap your second select statement with sub-query : 

select avg("JUMLAH") 
from(select d.nama,count(th.fk_distributor) as "JUMLAH"
     from mh_distributor d join 
          th_beli th on th.fk_distributor = d.kode
     group by d.nama
    ) t;

So, your full statement would be :

select d.kode, rpad(d.nama,75,' ') as "NAMA", 
      lpad(count(th.fk_distributor),10,' ') as "JUMLAH"
from mh_distributor d join 
     th_beli th 
     on th.fk_distributor = d.kode
having count(td.fk_produk) > (select avg("JUMLAH") 
                              from(select d.nama,count(th.fk_distributor) as "JUMLAH"
                                   from mh_distributor d join 
                                        th_beli th on th.fk_distributor = d.kode
                                   group by d.nama
                                 ) t
                             )
group by d.nama, td.fk_produk, d.kode
order by d.kode asc;

Upvotes: 1

Related Questions