Having clause without GROUP BY clause?

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.those queries actually run in oracle? i rather doubt it

without a GROUP BY, the entire table is considered a single group

the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

in the second query you will surely get a syntax error even in oracle

perhaps what you want for the two queries is:

SELECT SUM(col1) from test where col2 < 5

SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.

SELECT SUM(col1) from test HAVING SUM(col2) < 5

SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get

having clause and where clause

for the following SQL statement
select ...
group by col1
having col1 is not null
Does it always return the same result as
select ...
where col1 is not null
group by col1
?
The "where" one should have better performance, is it always true?Having gets evaluated after all the results have been returned and
aggregates calculated.
Where gets evaluated as the results are returned. At which point the filter
happens depends on the execution plan, but it will always be prior to
aggregation.
As a rule, only criteria on aggregate columns (sum, count, etc.) should be
in the having clause. Everything else belongs in the where.
Where SHOULD always be more efficient than having. HOWEVER, it is possible
(but highly unlikely) that the difference could cause SQL Server to use a
different execution plan that by some freak coincidence would perform better
with the having than the where. This would be the exception, and I would be
rather surprised if anyone could come up with such a case.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||They should return the same result. However, the version with the where
clause is (IMHO) better code. Where should be used when you want to compare
on the values in each row, use haveing when you want to compare on
aggregated values. So the SQL to retrive all customers with orders totaling
more than $1000 from New York would be something like:
Select ...
Where State = 'NY'
Group By CustomerID
Having Amount > 1000
Tom
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||Use having ONLY if you cannot achieve the functionality through the where
clause.
In case of having the result set is already generated and the filter is
applied.
And in this particular case that you have mentioned there won't be any
difference in the result (or so I think).