Re: simple sql question - group by having ?

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 23 Apr 2007 11:44:58 -0700
Message-ID: <1177353898.947410.275700@n76g2000hsh.googlegroups.com>

DA Morgan wrote:
> jobs wrote:
>
> SELECT cola, colb, colc
> FROM t
> WHERE cola IN (SELECT cola FROM (your_query_here));
>
> One way among many. Also look at using EXISTS.
>

Actually no need to scan table more than once.

assuming you haven't too ancient Oracle version: SQL> desc test

 Name                                      Null?    Type
 ----------------------------------------- --------
---------------------
 TST_ID                                    NOT NULL NUMBER
 TST_OBJ_NAME                                       VARCHAR2(100 CHAR)

SQL> ed
Wrote file afiedt.buf

  1 select tst_obj_name, count(*)
  2 from test
  3 group by tst_obj_name
  4* having count(*) >10
SQL> / TST_OBJ_NAME

COUNT(*)

COSTS
13

COSTS_PROD_BIX
13

COSTS_TIME_BIX
13

SALES
17

SALES_CHANNEL_BIX
17

SALES_CUST_BIX
17

SALES_PROD_BIX
17

SALES_PROMO_BIX
17

SALES_TIME_BIX
17

9 rows selected.

SQL> ed
Wrote file afiedt.buf

  1 select * from (
  2 select tst_obj_name, tst_id, count(*) over (partition by tst_obj_name) cnt
  3 from test
  4 )
  5* where cnt > 10
SQL> / TST_OBJ_NAME

TST_ID CNT
---------- ----------
COSTS
28572 13

COSTS
28575 13

COSTS
28577 13

Hopefully original OP will be able to adjust this to your table. Analytic functions rules! :)

Gints Plivna
http://www.gplivna.eu Received on Mon Apr 23 2007 - 13:44:58 CDT