On Jun 25, 12:49 am, "faizal mangattil`" <faizmangat...@gmail.com>
wrote:
> select column1, count(*) from table1 group by column1 having count(column1)
>
>
>
> > 99;
> On Wed, Jun 24, 2009 at 4:04 PM, Rob Wolfe <wolfe....@gmail.com> wrote:
>
> > On Jun 24, 7:24 am, Pav <ksulta...@googlemail.com> wrote:
> > > Hi
>
> > > I was trying to run an sql statement as following
>
> > > select column1, count(*) from table1 group by column1 where count
> > > (column1) > 99;
>
> > > This seems to be incorrect, and so I was wondering if anyone knew a
> > > working alternative?
>
> > for a group expression you use HAVING not WHERE
>
> > so would be more like
>
> > select column1, count(*)
> > from table1
> > group by column1
> > having count(*) > 99
>
> > I am presuming that you only want the rows where the count is > 99
>
> > Rob- Hide quoted text -
>
> - Show quoted text -
The original statement had no typographical errors; if you're coding
your count() against a specific column then do so througout the entire
query:
select column1, count(column1) from table1 group by column1 having
count(column1) > 99;
Your example is also missing the relatiohnal operator and defining
condition (> 99) so your statement generates an error:
SQL> select column1, count(*) from table1 group by column1 having count
(column1)
2 /
select column1, count(*) from table1 group by column1 having count
(column1)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL>
Rob's original statement is correct and functions as expected:
SQL> select column1, count(*)
2 from table1
3 group by column1
4 having count(*) > 99
5
SQL>
SQL> /
COLUMN1 COUNT(*)
---------- ----------
1 589
6 588
11 588
13 588
2 589
14 588
4 589
5 588
8 588
3 589
7 588
COLUMN1 COUNT(*)
---------- ----------
9 588
10 588
12 588
15 588
16 588
0 588
17 rows selected.
SQL>
Changing his 'count(*)' to 'count(column1)' makes no difference:
SQL> select column1, count(*)
2 from table1
3 group by column1
4 having count(column1) > 99;
COLUMN1 COUNT(*)
---------- ----------
1 589
6 588
11 588
13 588
2 589
14 588
4 589
5 588
8 588
3 589
7 588
COLUMN1 COUNT(*)
---------- ----------
9 588
10 588
12 588
15 588
16 588
0 588
17 rows selected.
SQL>
Your statement is no more efficient, either:
SQL> select column1, count(*)
2 from table1
3 group by column1
4 having count(column1) > 99;
COLUMN1 COUNT(*)
---------- ----------
1 589
6 588
11 588
13 588
2 589
14 588
4 589
5 588
8 588
3 589
7 588
COLUMN1 COUNT(*)
---------- ----------
9 588
10 588
12 588
15 588
16 588
0 588
17 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2160075778
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 8 (13)|
00:00:01 |
|* 1 | FILTER | | | |
| |
| 2 | HASH GROUP BY | | 10000 | 126K| 8 (13)|
00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE1 | 10000 | 126K| 7 (0)|
00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT("COLUMN1")>99)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
264 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
785 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
17 rows processed
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select column1, count(*)
2 from table1
3 group by column1
4 having count(*) > 99 ;
COLUMN1 COUNT(*)
---------- ----------
1 589
6 588
11 588
13 588
2 589
14 588
4 589
5 588
8 588
3 589
7 588
COLUMN1 COUNT(*)
---------- ----------
9 588
10 588
12 588
15 588
16 588
0 588
17 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2160075778
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 8 (13)|
00:00:01 |
|* 1 | FILTER | | | |
| |
| 2 | HASH GROUP BY | | 10000 | 126K| 8 (13)|
00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE1 | 10000 | 126K| 7 (0)|
00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>99)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
264 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
785 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
17 rows processed
SQL>
I guess I don't understand why someone would post an incomplete
statement as a 'correction' to a functioning query.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
