HAVING and GROUP BY

Hi All
I want to have all ideas about the use
of HAVING and GROUP BY
When either of them can be used?
Can HAVING be used without GROUP BY? HOW? WHY?
Can HAVING be used without an aggregate function? HOW? WHY?
Guys, you will certainly be doing me a favour and all other
newbie who access this group but find advanced discussions
going over the head due to less clear basics.
Thanks in Advance
Regards
Suresh BeniwalRefer Books online
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128424024.788067.225630@.g44g2000cwa.googlegroups.com...
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
> When either of them can be used?
> Can HAVING be used without GROUP BY? HOW? WHY?
> Can HAVING be used without an aggregate function? HOW? WHY?
> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal
>|||Look in the BOl for further details about GROUP BY and HAVING. In
Common using Group by groups the resultset for the specified columns:
Select companyName
>From Customer
Group by companyName
HAVING is used to filter down the grouped result by the expression you
issue within the HAVING clause (e.g. HAVING COUNT(*) > 1, showing only
these values that appear more than one time within
Select companyName
>From Customer
Group by companyName
Having COUNT(*) > 1
Samples out there, e.g.
http://msdn.microsoft.com/library/d...r />
_1l4j.asp
HTH, Jens Suessmeyer.|||SureshBeniwal wrote:
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
Check out BOL, and posts like this:
http://groups.google.nl/group/micro...de=source&hl=nl

> When either of them can be used?
GROUP BY if you want to aggregate information on groups of data. HAVING
if you want to filter the result based on the aggregated information. If
you want to filter the result based on base columns, then I suggest you
use the WHERE clause

> Can HAVING be used without GROUP BY?
Yes

> HOW?
By omitting the GROUP BY clause, and by only specifying aggregate
functions

> WHY?
Because the entire selection will be interpreted as a single group. This
is also why non-aggregate functions are not allowed when specifying a
HAVING clause without GROUP BY clause

> Can HAVING be used without an aggregate function?
Yes

> HOW?
By using selection list items in the predicates. But that is not very
useful, because in these cases the HAVING clause will act as a WHERE
clause, so you could just as well move the predicates from the HAVING to
the WHERE clause

> WHY?
Because the SQL standard allows it. I can see no reason why it should
not be allowed
HTH,
Gert-Jan

> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal|||Thanks all of you
that was sure an insight
but only i missed some practical examples
anyways...thanks
bye