On Jun 25, 7:07 am, Rob Wolfe <wolfe....@gmail.com> wrote:
> 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 -- Hide quoted text -
>
> - Show quoted text -
And I, just yesterday, put to use the pragma autonomous_transaction
construct in a function intended to perform inserts. You'd have
thought the heavens had opened wide with angels singing praises.
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
-~----------~----~----~----~------~----~------~--~---
