Basic Aggregate/HAVING question  
Author Message
jsnelson





PostPosted: Wed Dec 03 17:20:16 CST 2003 Top

SQL Server >> Basic Aggregate/HAVING question

I want to find the order that has the largest quanity in pubs.dbo.sales

If I try to use an aggregate in the WHERE clause I get an error
--
SELECT ord_num
FROM pubs.dbo.sales
WHERE qty=max(qty)
/*
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause
or a select list, and the column being aggregated is an outer reference.
*/


But I am almost certain that I used to be able to do this in Sybase T-SQL
and it worked:

SELECT ord_num
FROM pubs.dbo.sales
HAVING qty=max(qty)

Now in MS T-SQL I get these errors:

/*
Column 'sales.ord_num' is invalid in the select list because it is not
contained in an aggregate function
and there is no GROUP BY clause.

Column 'sales.qty' is invalid in the HAVING clause because it is not
contained in an aggregate function
and there is no GROUP BY clause.
*/


It looks like I can get what I want by either one of two ways

1. A subquery:

SELECT ord_num, qty
FROM pubs.dbo.sales
WHERE qty=(SELECT max(qty) FROM sales)
/*
ord_num qty
-------------------- ------
QA7442.3 75
*/


2. A GROUP BY with TOP

SELECT TOP 1 ord_num, max(qty) AS 'qty'
FROM pubs.dbo.sales
GROUP BY ord_num
ORDER BY max(qty) DESC

/*
ord_num qty
-------------------- ------
QA7442.3 75
*/


I have 2 questions:

1. Are there other (better) ways to write this?

2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?

SQL Server258  
 
 
David





PostPosted: Wed Dec 03 17:20:16 CST 2003 Top

SQL Server >> Basic Aggregate/HAVING question > But I am almost certain that I used to be able to do this in Sybase T-SQL
> and it worked:
>
> SELECT ord_num
> FROM pubs.dbo.sales
> HAVING qty=max(qty)

Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause is
applied after the aggregation so it doesn't make sense to reference a base
table column in this way unless it exists in the aggregate result.


> 1. Are there other (better) ways to write this?

I doubt it's any "better" but just for variety here's an alternative:

SELECT ord_num, qty
FROM Sales
WHERE qty >= ALL
(SELECT qty
FROM Sales)

I prefer the WHERE qty= subquery method.


> 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?

Yes. HAVING without GROUP BY implies an aggregation across the whole set in
the same way that an aggregate function does without GROUP BY. The result
will be, at most, one row. You can use HAVING to return a value only if some
condition is met:

SELECT MIN(ord_date)
FROM Sales
HAVING MIN(ord_date)<='20000101'

And it can be especially useful in conjunction with EXISTS:
...
EXISTS -- Is ord_date unique?
(SELECT 1
FROM Sales
HAVING COUNT(DISTINCT ord_date)=COUNT(*))

--
David Portas
------------
Please reply only to the newsgroup
--


 
 
DaveF





PostPosted: Thu Dec 04 15:25:18 CST 2003 Top

SQL Server >> Basic Aggregate/HAVING question Thanks David for the detailed answers.





> > But I am almost certain that I used to be able to do this in Sybase
T-SQL
> > and it worked:
> >
> > SELECT ord_num
> > FROM pubs.dbo.sales
> > HAVING qty=max(qty)
>
> Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
> syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause
is
> applied after the aggregation so it doesn't make sense to reference a base
> table column in this way unless it exists in the aggregate result.
>
>
> > 1. Are there other (better) ways to write this?
>
> I doubt it's any "better" but just for variety here's an alternative:
>
> SELECT ord_num, qty
> FROM Sales
> WHERE qty >= ALL
> (SELECT qty
> FROM Sales)
>
> I prefer the WHERE qty= subquery method.
>
>
> > 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so,
when?
>
> Yes. HAVING without GROUP BY implies an aggregation across the whole set
in
> the same way that an aggregate function does without GROUP BY. The result
> will be, at most, one row. You can use HAVING to return a value only if
some
> condition is met:
>
> SELECT MIN(ord_date)
> FROM Sales
> HAVING MIN(ord_date)<='20000101'
>
> And it can be especially useful in conjunction with EXISTS:
> ...
> EXISTS -- Is ord_date unique?
> (SELECT 1
> FROM Sales
> HAVING COUNT(DISTINCT ord_date)=COUNT(*))
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>