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:
>