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

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.