Why does SQL server require this ???

I am SELECTing several fields and want to group by 3 of the fields. However it requires me to include all fields in the group by clause.

Why is this ??? Why can't you select a couple fields to group by like in crystal reports ?

Am I doing something wrong ?

Are you doing an aggregates in your query?

Yes.... I am doing a SUM on a field called dozens. And want it to group on all styles/sizes/colors that are alike. But it is requiring me to group by other fields as well... Fields I don't want to group by.

How would the query know what to do a SUM for if you don't tell it what the grouping qualifiers are? Otherwise it is just a line item report not a summary.

Edit: Well if there are fields you don't want to GROUP By then they need some kind of Aggregate function Average/Max/Min etc.. everything in the GROUP BY statement is something not given an aggregate function. And this is true for I think all databases that use ANSI Standard SQL.

When you do any type of aggregate functions, all fields in your SELECT statement NOT in an aggregate function, must be included in a GROUP BY clause.

Quote:

---

Why can't you select a couple fields to group by like in crystal reports ?

---

Crystal is either generating the SQL Statement with all fields in the Group By clause or it is processing the grouping internally.

To see the sql statement that Crystal is executing use DataBase -> Show SQL Query menu item.

When you use a SUM function on a particular column, it is required that all other columns be part of the "grouping" determination or also be in an aggregate function.

That's a basic concept that cannot be gotten around - not a requirement at all - a basic foundation of what GROUP BY is doing.

Select FRUIT, COLOR, SUM(PRICE) From SomeTable Group By FRUIT

Of course that cannot work - you have RED and YELLOW apples - what is the SUM(PRICE) going to refer to??

There are ways to use SUB-QUERIES to get around issues like this, but in effect the SUB-QUERY can only return one row anyway - so it's basically an AGGREGATE function in and of itself.

There are also ways to build temporary tables (or table variables if you are in MS SQL SERVER) that can help you build up your final resultset with your desired results.

Give more details of the columns in your table and query and a more detailed explanation of what you desired result is...

It's not really anything to do with aggregate functions. If you think about it, if you have a field in the select statement that isn't in the group by clause it might (and probably would) have several values in it.

Using Szlamany's fruit example, if your SQL statement was select fruit, colour from myTable group by fruit and you have red, green and yellow apples, which value, of the three possible ones, should be returned in the result set for the apple row. The answer is inexact and can't be expressed.

Aggregate functions don't need to be in the group by list because they express a value that applies across the whole of each group, so they can be expressed as a single value for each row.