sql server programming: I’m getting an error I don’t completely understand while trying to do
some homework for an Intro to Database Management class. Just to give
some reference, here is what the question asks:

"Create a view called CustOrder which contains the customer number,
name, balance, order number, and order date for every order currently
on file. Display the data in the view."

I take it that they want me to group the results by Order so my code
looks like this:

Create View CustOrder As
Select Customer.CustomerNum, Customer.CustomerName, Balance,
Orders.OrderNum, Orders.Orderdate
From Customer, Orders
Group By Orders.OrderNum

When I run that I get these errors:

Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.CustomerNum’ is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.CustomerName’ is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.Balance’ is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Orders.OrderDate’ is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

I take it that that meant I had to include all the other columns in
the Group By statement; but when I do this, it does not display
correctly. From researching this error on Google I found the reason
why this is required:

"In SQL Server, as per the ANSI specification, non-aggregated columns
must be explicitly grouped using the GROUP BY clause."

So does this mean there is no way to display the data as the question
asks while using SQL Server 2000? Many thanks in advance.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming-simple-ftopict242763.html

Please post the DDL for the tables involved.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
I’m getting an error I don’t completely understand while trying to do
some homework for an Intro to Database Management class. Just to give
some reference, here is what the question asks:

"Create a view called CustOrder which contains the customer number,
name, balance, order number, and order date for every order currently
on file. Display the data in the view."

I take it that they want me to group the results by Order so my code
looks like this:

Create View CustOrder As
Select Customer.CustomerNum, Customer.CustomerName, Balance,
Orders.OrderNum, Orders.Orderdate
From Customer, Orders
Group By Orders.OrderNum

When I run that I get these errors:

Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.CustomerNum’ is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.CustomerName’ is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Customer.Balance’ is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Procedure CustOrder, Line 4
Column ’Orders.OrderDate’ is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

I take it that that meant I had to include all the other columns in
the Group By statement; but when I do this, it does not display
correctly. From researching this error on Google I found the reason
why this is required:

"In SQL Server, as per the ANSI specification, non-aggregated columns
must be explicitly grouped using the GROUP BY clause."

So does this mean there is no way to display the data as the question
asks while using SQL Server 2000? Many thanks in advance.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming-simple-ftopict242763.html
Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=843509

Xee

What in the question makes you think you need to group? It sounds like you
have a table of orders, and you want some of the data from every order. What
needs to be grouped?

Grouping is used when you have lots of duplicate values in some column, but
you want to display one row for each distinct value. Nothing in your
description indicates any duplicate values. Maybe you left something out.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]