Re: GROUP BY ALL not as expected

Manish Negandhi <negandhi.manish@xxxxxxxxx> wrote:
Here is what Sybase says for this behaviour

"If any columns in the select list do not have aggregate functions
applied to them and are not included in the query's group by clause
(illegal in standard SQL), the meanings of having and where are
somewhat different.
In this situation, a where clause restricts the rows that are included
in the calculation of the aggregate, but does not restrict the rows
returned by the query. Conversely, a having clause restricts the rows
returned by the query, but does not affect the calculation of the
aggregate. See group by and having clauses for examples."

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/68329;pt=68329#X

Thanks
Manish


True, but not applicable for this query. All the columns in the select
list that do not have aggregate functions applied to them are included
in the group by clause.

Luc.





On Oct 10, 6:40 pm, --CELKO-- <jcelko...@xxxxxxxxxxxxx> wrote:
I don't know what the sql standard says about this .. <<

Nothing; this is not Standard SQL. Look up "grouping sets", "rollup"
and "cube" for the newer extensions to GROUP BY.


.

Relevant Pages

  • Re: Distinguish between same field names in 2 tables
    ... Whenever you're using aggregate functions such as Count, ... query must either be a call to one of the aggregate functions, ... WHERE clause. ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: error message.
    ... Since you are using one of the aggregate functions (SUM) in the query, you need to group by all of the other fields that are not using the sum function. ... You might need to replace the SUM with the VBA function DSUM or with a subquery in the Select clause; and drop the entire Group by clause. ...
    (microsoft.public.access.queries)
  • Re: error while executing this query
    ... So You can only use aggregate functions there. ... The filter conditions in your query can be achieved using the where clause. ... e) Go to the SELECT clause and construct the expressions in the list. ...
    (microsoft.public.sqlserver.programming)
  • Re: Multiple rows to one row
    ... add a GROUP BY clause and use the CONCATENATE set function in ... the SELECT clause. ... SQL Aggregate Functions ...
    (microsoft.public.access.queries)
  • Re: SQL syntax error
    ... All of your returned columns that are not aggregate functions need to be ... Column 't_po_master.season' is invalid in the select list because it is ... not contained in either an aggregate function or the GROUP BY clause. ...
    (microsoft.public.sqlserver.programming)