SQL Server 2008 View Group By

Question

I have created the following view in SQL Server 2008 to create mailing lists for land owners:

SELECT     
   dbo.parcel.featid, 
   CAST(mms_db.dbo.TR_Roll_Master.FMT_ROLL_NO AS decimal(11, 3)) AS Roll, 
   dbo.parcel.survey, mms_db.dbo.Central_Name_Database.NAME AS Owner,
   mms_db.dbo.Central_Name_Database.NAME_2 AS Owner2,
   mms_db.dbo.Central_Name_Database.BOX_NUM, 
   mms_db.dbo.Central_Name_Database.APT_NUM, 
   mms_db.dbo.Central_Name_Database.FMT_STREET AS House_num,
   mms_db.dbo.Central_Name_Database.CITY AS Town, 
   mms_db.dbo.Central_Name_Database.PROV_CD AS Prov, 
   mms_db.dbo.Central_Name_Database.POST_CD AS Post_code,
   mms_db.dbo.TR_Roll_Number_Owners.NAME_CODE 
FROM
   mms_db.dbo.TR_Roll_Master 
INNER JOIN 
   dbo.parcel ON mms_db.dbo.TR_Roll_Master.ROLL_NO = dbo.parcel.roll_no COLLATE SQL_Latin1_General_CP1_CI_AS 
INNER JOIN 
   mms_db.dbo.TR_Roll_Number_Owners ON mms_db.dbo.TR_Roll_Master.ROLL_NO = mms_db.dbo.TR_Roll_Number_Owners.ROLL_NO 
INNER JOIN 
   mms_db.dbo.Central_Name_Database ON mms_db.dbo.TR_Roll_Number_Owners.NAME_CODE = mms_db.dbo.Central_Name_Database.NAME_CODE
WHERE     
   (mms_db.dbo.TR_Roll_Master.DEL_ROLL NOT LIKE '%Y%') AND
   (mms_db.dbo.TR_Roll_Master.ROLL_NO NOT LIKE 'P%') OR
   (mms_db.dbo.TR_Roll_Master.DEL_ROLL IS NULL) AND (mms_db.dbo.TR_Roll_Master.ROLL_NO NOT  LIKE 'P%') OR
   (mms_db.dbo.TR_Roll_Master.DEL_ROLL NOT LIKE '%I%') AND
   (mms_db.dbo.TR_Roll_Master.ROLL_NO NOT LIKE 'P%')

The view works fine however there are often duplicates as many people own more than one piece of land. I would like to group by Name_Code to eliminate the duplicates.

When I add:

 Group by mms_db.dbo.TR_Roll_Number_Owners.NAME_CODE

to the end of the query I am returned with the following response:

SQL Execution Error.

Executed SQL statement: SELECT dbo.parcel.featid, CAST(mms_db.dbo.TR_Roll_Master.FMT_ROLL_NO AS decimal(11,3)) AS Roll, dbo.parcel.survey, mms_db.dbo.Central_NameDatabase.Name AS Owner, mms_db.dbo.Central_Name_Database.NAME_2 AS Owner2, mms_db.dbo.Central_Name_Database.B... Error Source: .Net SQLClient Data Provider Error Message: Column 'dbo.parcel.featid' is invalid in the select list because it is not contained in either an aggregate function or the
GROUP BY clause.

I'm not sure what I need to change to make this work.

--Edit--

As a sample data, here is a condensed sample of what I would like to achieve

Roll       Owner    Box_Num        Town    Prov     Post_code    Name_Code

100     John Smith     50       Somewhere   MB      R3W 9T7       00478
200     John Smith     50       Somewhere   MB      R3W 9T7       00478
300     Peter Smith    72       Somewhere   MB      R3W 9T9       00592
400     John Smith     90       OtherPlace  MB      R2R 8V7       00682

John Smith has the name code of 00478. He owns both Roll 100 & 200, Peter Smith owns 300 and another person with the name of John Smith owns 400. Based on different Name_Code values I know that the two John Smith's are different people. I would like an output that would list John Smith with Name_Code 00478 1 time only while also listing Peter Smith and the other John Smith. Name_Code is the only value I can use for grouping as the rest could represent different people with the same name.

For the `GROUP BY` clause you need to add the items in the order you want them grouped until you reach the end of the grouping, try adding the `dbo.parcel.featid` to the `GROUP BY` by something like `GROUP BY mms_db.dbo.TR_Roll_Number_Owners.NAME_CODE, dbo.parcel.featid` — MCP_infiltrator, Jan 09 '14 at 16:45
Can you show some sample data, explaining which row you want to keep and why? — Aaron Bertrand, Jan 09 '14 at 16:46
See http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e — Joe Rinehart, Jan 09 '14 at 16:46

score 1 · Answer 1 · edited May 23 '17 at 11:56

If you just want to eliminate duplicates, just use DISTINCT and exclude the columns representing other "people on more than one piece of land" from your query viz:

SELECT DISTINCT 
   NAME_CODE,
   {column2},
   {column3},
FROM
   [MyView]

However, if you wish to perform aggregation of some sort, or show one random of the "people on more than one piece of land" then you will need the GROUP BY. All non-aggregated columns in the select need to appear in the group by:

SELECT
   NAME_CODE,
   ... Other non aggregated fields here
   COUNT(featid) AS NumFeatIds,
   MIN(Owner2) AS FirstOwner,
   ... etc (other aggregated columns)
GROUP BY 
   NAME_CODE,
   ... All non-aggregated columns in the select.

Edit

To get the table listed in your edit, you would just need to ORDER BY Name_Code

However to get just one row of John Smith #00478, you need to compromise on the non-unique columns by either eliminating them entirely, using GROUP BY and aggregates on the rows, doing a GROUP_CONCAT type hack to e.g. comma separate them, or to pivot the duplicate row columns as extra columns on the one row.

Since you've mentioned GROUP repeatedly, it seems the aggregation route is necessary. John Smith #00478 has 2 properties, hence 2 discrete Roll values. So Roll can't appear in the aggregated result. So instead, you can return e.g. a count of the Rolls, or the MIN or MAX Roll, but not both Rows*. The other columns (Address related) are probably constant for all properties (assuming John Smith 00478 has one address), but unfortunately SqlServer will require you to include them in the GROUP.

I would suggest you try:

SELECT 
    COUNT(Roll) AS NumPropertiesOwned, 
    Owner, 
    Box_Num, 
    Town, 
    Prov, 
    Post_code, 
    Name_Code
FROM [MyNewView]
GROUP BY 
    Owner, Box_Num, Town, Prov, Post_code, Name_Code
ORDER BY Name_Code;

i.e. all the non-aggregated columns must be repeated in the GROUP BY

_{_{* unless you use the GROUP_CONCAT hack or the pivot route}}

I tried the Select Distinct function however it didn't work as the only distinct field I have is the Name_code. For example there could be more than 1 person with the same name, its the code that will differentiate them. The other fields such as "roll" aren't necessarily distinct. — Jeremy, Jan 09 '14 at 17:45
@jeremey, you have to understand that you do not have duplicate rows, so grouping or distinct is not going to help you. Duplicate rows are only duplicates is every value for every column is the same. If one column differs then you have different rows. That is the way SQL works and the way it should work. — HLGEM, Jan 09 '14 at 19:53

score 0 · Accepted Answer · answered Jan 09 '14 at 16:45

0

its telling you what to do:

"Error Message: Column 'dbo.parcel.featid' is invalid in the select list
because it is not contained in either an aggregate function or the  
GROUP BY clause."

This means you have to group the other (non-aggregated) fields too.

answered Jan 09 '14 at 16:45

Rich

867
7
12

I don't want to group by these other fields. Is there a way to aggregate the other fields so that it allows me to group by only the name_code field? – Jeremy Jan 09 '14 at 17:02
@jeremey, you8 cannot gropu by only one field. It is not allowed. – HLGEM Jan 09 '14 at 19:51

SQL Server 2008 View Group By

2 Answers2