David, thank you for confirming that I wasn't nuts with the original
query. It was a wee bit early for me and since the coffee hadn't
kicked in (and I was too lazy to re-read it) assumed I had misread the
original that the poster wrote.
Considering the number of times a day that i use the having clause one
would think that I would have confidence in my memory even before
breakfast.
On another quick note, I want to give a pat on the back to Mike. I
used his xml based method of concatenating rows into a single column
yesterday and got one of my developers thinking I was some sort of
magician. We old folks have to prove to the kids every now and then
that we actually do keep learning :-)
Rob
On Jun 25, 7:35 am, ddf <orat...@msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
