GROUP BY/select list error

I am trying to get a rollup report with several summaries included, but I
keep getting errors that complain about columns not in the GROUP by included
in the select list , even though are not. They are used in a subquery and a
join however. If I use the integer IDs the query works fine, but I need to
order by the names rather than IDs so I get an alphabetical report.
Given the snippet that follws can anyone tell me what might be causing the
issue and how I can correct it?
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision
WITH ROLLUP
Results in the errors:
Msg 8120, Level 16, State 1, Line 1
Column 'P.LotPropertyID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'S.LotSubdivisionID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause."Byron" <Byron@.discussions.microsoft.com> wrote in message
news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
>I am trying to get a rollup report with several summaries included, but I
> keep getting errors that complain about columns not in the GROUP by
> included
> in the select list , even though are not. They are used in a subquery and
> a
> join however. If I use the integer IDs the query works fine, but I need
> to
> order by the names rather than IDs so I get an alphabetical report.
> Given the snippet that follws can anyone tell me what might be causing the
> issue and how I can correct it?
>
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision
> WITH ROLLUP
>
> Results in the errors:
> Msg 8120, Level 16, State 1, Line 1
> Column 'P.LotPropertyID' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Msg 8120, Level 16, State 1, Line 1
> Column 'S.LotSubdivisionID' is invalid in the select list because it is
> not
> contained in either an aggregate function or the GROUP BY clause.
You are trying to referencing unaggregated columns in the subquery:
...
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
That won't work. I can only guess what you intended by this query. Try the
following but if that's not it please post DDL, sample data and show your
required end result.
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(*) AS lots
FROM dbo.tbl_Lots L (NOLOCK)
JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
S.LotSubdivision
WITH ROLLUP ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Below are the tables involved, less many of the uninvolved columns.
Properties have Lots and Subdivisions and Lots are assigned to both a
Property and one of its Subdivisions. The general idea is to get the number
of Lots in each Subdivision subtotalled by Property, followed by a grand
total. The problem is that I have to group by integer IDs, but want the
results displayed by Property name, further broken out by Subdivision name.
For example:
Property 1 Subdivision 1 23
Property 1 Subdivision 2 15
Property 1 38
Property 2 Subdivision 1 10
Property 2 Subdivision 2 10
Property 2 20
All Properties 58
CREATE TABLE [dbo].[tbl_LotProperties](
[LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyName] [nvarchar](128))
CREATE TABLE [dbo].[tbl_LotSubdivisions](
[LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivision] [nvarchar](100))
CREATE TABLE [dbo].[tbl_Lots](
[LotID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivisionID] [int] NOT NULL)
"David Portas" wrote:

> "Byron" <Byron@.discussions.microsoft.com> wrote in message
> news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
> You are trying to referencing unaggregated columns in the subquery:
> ...
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> That won't work. I can only guess what you intended by this query. Try the
> following but if that's not it please post DDL, sample data and show your
> required end result.
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(*) AS lots
> FROM dbo.tbl_Lots L (NOLOCK)
> JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
> S.LotSubdivision
> WITH ROLLUP ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||Just add the integer IDs to the select list (generally). It's a major
problem that you don't have any PRIMARY KEYs set on your tables. I
assume that the PKs are the "ID"s, though. Just make sure that you
define your data that way. So, you could do:
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
GROUP BY
P.LotPropertyID,
P.LotPropertyName,
S.LotSubDivisionID,
S.LotSubdivision
WITH ROLLUP
but like Dave Portas alluded to, here's a better way (btw, I am "reading
in" to your requirements a bit here):
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(DISTINCT L.LotID) AS Lots
FROM dbo.tbl_Lots L (NOLOCK)
INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
WITH ROLLUP;
Byron wrote:
> Below are the tables involved, less many of the uninvolved columns.
> Properties have Lots and Subdivisions and Lots are assigned to both a
> Property and one of its Subdivisions. The general idea is to get the numb
er
> of Lots in each Subdivision subtotalled by Property, followed by a grand
> total. The problem is that I have to group by integer IDs, but want the
> results displayed by Property name, further broken out by Subdivision name
.
> For example:
> Property 1 Subdivision 1 23
> Property 1 Subdivision 2 15
> Property 1 38
> Property 2 Subdivision 1 10
> Property 2 Subdivision 2 10
> Property 2 20
> All Properties 58
> CREATE TABLE [dbo].[tbl_LotProperties](
> [LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyName] [nvarchar](128))
> CREATE TABLE [dbo].[tbl_LotSubdivisions](
> [LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivision] [nvarchar](100))
> CREATE TABLE [dbo].[tbl_Lots](
> [LotID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivisionID] [int] NOT NULL)
>
> "David Portas" wrote:
>|||I apparently erred by simplifying my example code to save space. There are
actually primary keys on the integer IDENTITY columns, and there are many
other columns I eliminated from the CREATE code, leavong only the keys and
names. There are also about a dozen other subqueries in addition to the one
that counts the lots, though all of them use data in the Lots table and need
to be grouped by Property and Subdivision. All of them work fine as long as
I group by ID, but I need to return the results to the user with the Propert
y
and Subdivision names sorted by name rather than ID. Since I ran into so
much trouble using GROUP BY I started down the path of using temporary table
s
and a cursor to iterate through an intermediate result set adding the
subtotals and grand total, but I realize there must be a better way to do it
.
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> Just add the integer IDs to the select list (generally). It's a major
> problem that you don't have any PRIMARY KEYs set on your tables. I
> assume that the PKs are the "ID"s, though. Just make sure that you
> define your data that way. So, you could do:
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
> GROUP BY
> P.LotPropertyID,
> P.LotPropertyName,
> S.LotSubDivisionID,
> S.LotSubdivision
> WITH ROLLUP
> but like Dave Portas alluded to, here's a better way (btw, I am "reading
> in" to your requirements a bit here):
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(DISTINCT L.LotID) AS Lots
> FROM dbo.tbl_Lots L (NOLOCK)
> INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
> WITH ROLLUP;
>
> Byron wrote:
>|||Post your real DDL and query. You don't want to be using a cursor for
this...
Byron wrote:
> I apparently erred by simplifying my example code to save space. There ar
e
> actually primary keys on the integer IDENTITY columns, and there are many
> other columns I eliminated from the CREATE code, leavong only the keys and
> names. There are also about a dozen other subqueries in addition to the o
ne
> that counts the lots, though all of them use data in the Lots table and ne
ed
> to be grouped by Property and Subdivision. All of them work fine as long
as
> I group by ID, but I need to return the results to the user with the Prope
rty
> and Subdivision names sorted by name rather than ID. Since I ran into so
> much trouble using GROUP BY I started down the path of using temporary tab
les
> and a cursor to iterate through an intermediate result set adding the
> subtotals and grand total, but I realize there must be a better way to do
it.
>
>
> "Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
>

GROUP BY - are JOINS the problem or is it Me?

Below is an SQL statement where I am trying to use a GROUP BY option. If I remove the GROUP BY it works fine but with the GROUP BY included it generates the following error detail: Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'GROUP'. I have tried removing the ORDER BY and it still fails. Is the GROUP BY operation limited by table JOINS or am I missing something really simple?

I am working with three tables: 1) [Stores] has the franchise type (S_Type - kiosk, mall store, free standing, etc) 2) [StoreInfo] has zip code and location info and 3) ZipLatLong has ZipCodes and their respective latitude and longitude. I want to creat a Distinct List of Stores.S_Type but in the result set I have several Stores.S_Types with different Zips thus the rows are not unique. I was expecting the GROUP BY statement to allow me to return a collection of unique Stores.S_Type. The Lat/Long WHERE segment is providing a geographical frame for the result set. Everything is working except for the GROUP BY.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:localStores1ConnectionString %>"SelectCommand="SELECT Stores.S_Type, StoreInfo.S_Zip, ZipLatLong.ZIPCode, ZipLatLong.Lat, ZipLatLong.Long FROM

StoreInfo INNER JOIN ZipLatLong ON StoreInfo.S_Zip = ZipLatLong.ZIPCode INNER JOIN Stores

ON StoreInfo.S_ID = Stores.S_ID WHERE (((ZipLatLong.Lat)< @.NLat AND (ZipLatLong.Lat)> @.SLat )) AND(((ZipLatLong.Long)< @.ELong AND

(ZipLatLong.Long)> @.WLong )) ORDER BY Stores.S_Type GROUP BY Stores.S_Type;">

The Group BY without an aggregate function is just a standard Distinct so SQL Server is telling you code is not correct, check the docs below and see adjustments you need and the second link download the code samples it will help you. One more thing I see you are doing calculations of Longitude and Latitude without a lot of Math functions that could be a problem, I would look for existing code for such complex calculations. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177673.aspx
http://mhprofessional.com/product.php?isbn=0072260939&cat=&pro

|||

I am using SQL 2000, is there any difference in the GROUP BY statements requirments for SQL 2000 vs. SQL 2005? I obviously do not yet understand the aggregate aspect of the coding and will read your links to hopefully gain the concept.

( The geographical mathematical work is done in its own class and the limits are passed into the SQL statement - without the GROUP BY statement everything works fine but I get multiple Store Types because they exist in different locations.)

|||

I don't think the GROUP BY clause changed check the link below for some advice about GROUP BY. Hope this helps.

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

group by

GROUP BY Clause
Specifies the groups into which output rows are to be placed and, if
aggregate functions are included in the SELECT clause <select list>,
calculates a summary value for each group. When GROUP BY is specified,
either each column in any non-aggregate expression in the select list should
be included in the GROUP BY list, or the GROUP BY expression must match
exactly the select list expression.
I don't understand the implication of having to include all columns in any
non-aggregate expression in the select list.
For example (from the "Commerce" ASP.NET Starter Kit) :
CREATE Procedure CMRC_CustomerAlsoBought
(
@.ProductID int
)
As
/* We want to take the top 5 products contained in
the orders where someone has purchased the given Product */
SELECT TOP 5
CMRC_OrderDetails.ProductID,
CMRC_Products.ModelName,
SUM(CMRC_OrderDetails.Quantity) as TotalNum
FROM
CMRC_OrderDetails
INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
CMRC_Products.ProductID
WHERE OrderID IN
(
/* This inner query should retrieve all orders that have contained the
productID */
SELECT DISTINCT OrderID
FROM CMRC_OrderDetails
WHERE ProductID = @.ProductID
)
AND CMRC_OrderDetails.ProductID != @.ProductID
GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
ORDER BY TotalNum DESC
CREATE TABLE [dbo].[CMRC_OrderDetails] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMRC_Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_Products] ADD
CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[CMRC_Orders] (
[OrderID]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMRC_Products] ADD
CONSTRAINT [FK_Products_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[CMRC_Categories] (
[CategoryID]
)
GOHi John

> I don't understand the implication of having to include all columns in any
> non-aggregate expression in the select list.
>
It is a requirement to do this otherwise you will get the error message such
as
Server: Msg 8120, Level 16, State 1, Line 5
Column 'CMRC_OrderDetails.ProductID' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause.
In your example as ProductID is the primary key for CMRC_Products it will
make no difference as all combinations of ProductID and ModelName are unique
.
HTH
John
"John Grandy" wrote:

> GROUP BY Clause
> Specifies the groups into which output rows are to be placed and, if
> aggregate functions are included in the SELECT clause <select list>,
> calculates a summary value for each group. When GROUP BY is specified,
> either each column in any non-aggregate expression in the select list shou
ld
> be included in the GROUP BY list, or the GROUP BY expression must match
> exactly the select list expression.
>
> I don't understand the implication of having to include all columns in any
> non-aggregate expression in the select list.
> For example (from the "Commerce" ASP.NET Starter Kit) :
>
> CREATE Procedure CMRC_CustomerAlsoBought
> (
> @.ProductID int
> )
> As
> /* We want to take the top 5 products contained in
> the orders where someone has purchased the given Product */
> SELECT TOP 5
> CMRC_OrderDetails.ProductID,
> CMRC_Products.ModelName,
> SUM(CMRC_OrderDetails.Quantity) as TotalNum
> FROM
> CMRC_OrderDetails
> INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
> CMRC_Products.ProductID
> WHERE OrderID IN
> (
> /* This inner query should retrieve all orders that have contained the
> productID */
> SELECT DISTINCT OrderID
> FROM CMRC_OrderDetails
> WHERE ProductID = @.ProductID
> )
> AND CMRC_OrderDetails.ProductID != @.ProductID
> GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
> ORDER BY TotalNum DESC
>
> CREATE TABLE [dbo].[CMRC_OrderDetails] (
> [OrderID] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [Quantity] [int] NOT NULL ,
> [UnitCost] [money] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CMRC_Products] (
> [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UnitCost] [money] NOT NULL ,
> [Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
> (
> [OrderID],
> [ProductID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
> (
> [ProductID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
> (
> [OrderID]
> ) REFERENCES [dbo].[CMRC_Orders] (
> [OrderID]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [FK_Products_Categories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[CMRC_Categories] (
> [CategoryID]
> )
> GO
>
>
>
>
>
>|||John,
A grouped query will not make sense if you don't follow this rule.
If you say
GROUP BY ProductID
you will retrieve one result row per ProductID. Suppose the table
you are querying contains 20 rows for ProductID number 123, and
additional columns Quantity and OrderID. If you ask for
select ProductID, OrderID, sum(Quantity)
group by ProductID
what do you want the query processor to do with the OrderID values
from the 20 ProductID=123 rows? The Quantity column will be summed,
but you can't return one result row (as the grouping requests) with 20
OrderID values - you either need to provide an aggregate to use on
the OrderID column or you need to change your mind and accept 20
rows in the result set by adding OrderID to the group by list.
Steve Kass
Drew University
John Grandy wrote:

>GROUP BY Clause
>Specifies the groups into which output rows are to be placed and, if
>aggregate functions are included in the SELECT clause <select list>,
>calculates a summary value for each group. When GROUP BY is specified,
>either each column in any non-aggregate expression in the select list shoul
d
>be included in the GROUP BY list, or the GROUP BY expression must match
>exactly the select list expression.
>
>I don't understand the implication of having to include all columns in any
>non-aggregate expression in the select list.
>For example (from the "Commerce" ASP.NET Starter Kit) :
>
>CREATE Procedure CMRC_CustomerAlsoBought
>(
> @.ProductID int
> )
>As
>/* We want to take the top 5 products contained in
> the orders where someone has purchased the given Product */
>SELECT TOP 5
> CMRC_OrderDetails.ProductID,
> CMRC_Products.ModelName,
> SUM(CMRC_OrderDetails.Quantity) as TotalNum
>FROM
> CMRC_OrderDetails
> INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
>CMRC_Products.ProductID
>WHERE OrderID IN
>(
> /* This inner query should retrieve all orders that have contained the
>productID */
> SELECT DISTINCT OrderID
> FROM CMRC_OrderDetails
> WHERE ProductID = @.ProductID
> )
>AND CMRC_OrderDetails.ProductID != @.ProductID
>GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
>ORDER BY TotalNum DESC
>
>CREATE TABLE [dbo].[CMRC_OrderDetails] (
> [OrderID] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [Quantity] [int] NOT NULL ,
> [UnitCost] [money] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[CMRC_Products] (
> [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UnitCost] [money] NOT NULL ,
> [Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
> (
> [OrderID],
> [ProductID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
> (
> [ProductID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
> (
> [OrderID]
> ) REFERENCES [dbo].[CMRC_Orders] (
> [OrderID]
> ) NOT FOR REPLICATION
>GO
>ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [FK_Products_Categories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[CMRC_Categories] (
> [CategoryID]
> )
>GO
>
>
>
>
>
>
>|||I have posted a detailed description of how a SELECT statement works.
Look it up and you can see why your mental model is wrong.|||Hi Steve, and thanks for the response.
So, when specifying a GROUP BY clause, the only variable is the order in
which you list the column names. Every GROUP BY clause must provide the
query processor with instructions regarding how to order the rows in the
subgroups of any group. An therefore every non-aggregate column must be
included.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23v3PIq8KFHA.3064@.TK2MSFTNGP12.phx.gbl...
> John,
> A grouped query will not make sense if you don't follow this rule.
> If you say
> GROUP BY ProductID
> you will retrieve one result row per ProductID. Suppose the table
> you are querying contains 20 rows for ProductID number 123, and
> additional columns Quantity and OrderID. If you ask for
> select ProductID, OrderID, sum(Quantity)
> group by ProductID
> what do you want the query processor to do with the OrderID values
> from the 20 ProductID=123 rows? The Quantity column will be summed,
> but you can't return one result row (as the grouping requests) with 20
> OrderID values - you either need to provide an aggregate to use on
> the OrderID column or you need to change your mind and accept 20
> rows in the result set by adding OrderID to the group by list.
> Steve Kass
> Drew University
>
> John Grandy wrote:
>|||Hi Joe, and thanks for the response.
Where is your description ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111164541.019670.34390@.o13g2000cwo.googlegroups.com...
>I have posted a detailed description of how a SELECT statement works.
> Look it up and you can see why your mental model is wrong.
>|||
John Grandy wrote:

>Hi Steve, and thanks for the response.
>So, when specifying a GROUP BY clause, the only variable is the order in
>which you list the column names. Every GROUP BY clause must provide the
>query processor with instructions regarding how to order the rows in the
>subgroups of any group. An therefore every non-aggregate column must be
>included.
>
I think you've got it, but just to make sure: there's nothing in a GROUP
BY query
that has anything to do with "how to order the rows", though that might
be your
interpretation of how a MIN or MAX aggregate is evaluated. There are other
aggregates, though, like AVG and COUNT, that don't correspond to the first
or last value in some order, as MIN and MAX do.
While changing the order of the column names in the GROUP BY clause
might change the order in which your results appear, think of any such
behavior as coincidental. If you want the result rows in a particular
order,
use an ORDER BY clause.
What the query processor needs to know in a grouping query is what
to do with the table source (those rows specified by what's in the FROM
and WHERE clauses. For each distinct combination of values of the
columns mentioned in the SELECT clause and in the group by clause,
you'll see one row in the result set. This row may correspond to one or
many rows in the table source, depending on how many times the
distinct combination appears. The additional columns of the result
set must all be aggregates, and each will represent a min, max,
avg, count, etc., for the one group of rows the result row corresponds
to.
So grouping queries give you one result row per group. The GROUP
BY clause identifies the columns used to specify each group, and the
remaining result columns are aggregate values for those groups.
SK

>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23v3PIq8KFHA.3064@.TK2MSFTNGP12.phx.gbl...
>
>
>|||You can find it here:
[url]http://groups.google.nl/groups?hl=nl&lr=&q=Here+is+how+a+SELECT+works+in+SQL+...+a
t+least+in+theory&btnG=Zoeken&meta=group%3Dmicrosoft.public.sqlserver.programming[
/url]
(url may wrap)
HTH,
Gert-Jan
John Grandy wrote:
> Hi Joe, and thanks for the response.
> Where is your description ?
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1111164541.019670.34390@.o13g2000cwo.googlegroups.com...|||Great explanation, Steve !
"Steve Kass" <skass@.drew.edu> wrote in message
news:eVVf4UCLFHA.244@.TK2MSFTNGP12.phx.gbl...
>
> John Grandy wrote:
>
> I think you've got it, but just to make sure: there's nothing in a GROUP
> BY query
> that has anything to do with "how to order the rows", though that might be
> your
> interpretation of how a MIN or MAX aggregate is evaluated. There are
> other
> aggregates, though, like AVG and COUNT, that don't correspond to the first
> or last value in some order, as MIN and MAX do.
> While changing the order of the column names in the GROUP BY clause
> might change the order in which your results appear, think of any such
> behavior as coincidental. If you want the result rows in a particular
> order,
> use an ORDER BY clause.
> What the query processor needs to know in a grouping query is what
> to do with the table source (those rows specified by what's in the FROM
> and WHERE clauses. For each distinct combination of values of the
> columns mentioned in the SELECT clause and in the group by clause,
> you'll see one row in the result set. This row may correspond to one or
> many rows in the table source, depending on how many times the
> distinct combination appears. The additional columns of the result
> set must all be aggregates, and each will represent a min, max,
> avg, count, etc., for the one group of rows the result row corresponds
> to.
> So grouping queries give you one result row per group. The GROUP
> BY clause identifies the columns used to specify each group, and the
> remaining result columns are aggregate values for those groups.
> SK
>