I am inserting a stored procedure that is supposed to display duplicate
rows, and it sort of works, but still non-duplicated rows show up sometimes.

The filtering statements were removed for testing, so much of the parameters
are not being used at this point.

Any help is appreciated.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PWQR_Select_Teardown_15_Day_Summary]
@Model_Year VARCHAR(8)=NULL,
@Plant_code VARCHAR(10)=NULL,
@Vehicle_Code VARCHAR(10)=NULL,
@Start_Date DATETIME=NULL,
@Stop_Date DATETIME=NULL,
@Shift VARCHAR(2)=NULL,
@Line VARCHAR(10)=NULL,
@Discrepancy VARCHAR(5)=NULL,
@Discrepancy_Count SMALLINT=2

AS
BEGIN

SET NOCOUNT ON;

SELECT
TWQRHISTEARDOWN_HEADER.WQR_TDH_MODELYEAR_T,
TWQRHISTEARDOWN_HEADER.WQR_TDH_PLANT_CODE_X,
TWQRHISTEARDOWN_HEADER.WQR_TDH_OFFLINEDT_Y,
TWQRHISTEARDOWN_HEADER.WQR_TDH_SHIFT_X,
TWQRHISTEARDOWN_HEADER.WQR_TDH_VEHICLE_CODE_X,
TWQRHISTEARDOWN_DETAIL.WQR_TDDTL_WELDGROUP_X,
TWQRHISTEARDOWN_DETAIL.WQR_TDDTL_WELDNUMBER_X,
TWQRHISTEARDOWN_DETAIL.WQR_TDDTL_DISCREPANCY_X,
TWQRHISTEARDOWN_DETAIL.WQR_TDDTL_ROBOT_X,
TWQRHISTEARDOWN_DETAIL.WQR_TDDTL_STATION_X,
COUNT(*)


FROM TWQRHISTEARDOWN_HEADER
INNER JOIN
TWQRHISTEARDOWN_DETAIL ON
TWQRHISTEARDOWN_HEADER.WQR_TDH_HEADER_ID =
TWQRHISTEARDOWN_DETAIL.WQRHISTTEARDOWN_HEADER_FK_K Y

GROUP BY
WQR_TDH_MODELYEAR_T,
WQR_TDH_PLANT_CODE_X,
WQR_TDH_OFFLINEDT_Y,
WQR_TDH_SHIFT_X,
WQR_TDH_VEHICLE_CODE_X,
WQR_TDDTL_WELDGROUP_X,
WQR_TDDTL_WELDNUMBER_X,
WQR_TDDTL_DISCREPANCY_X,
WQR_TDDTL_ROBOT_X,
WQR_TDDTL_STATION_X

HAVING (COUNT(*)>@Discrepancy_Count)


ORDER BY WQR_TDH_MODELYEAR_T,
WQR_TDH_PLANT_CODE_X,
WQR_TDH_OFFLINEDT_Y,
WQR_TDH_SHIFT_X,
WQR_TDH_VEHICLE_CODE_X,
WQR_TDDTL_WELDGROUP_X,
WQR_TDDTL_WELDNUMBER_X

END

What happens when you add the column WQR_TDH_HEADER_ID to both SELECT and
GROUP BY? If you get rows you consider non-duplicate show as duplicate, then
most likely your grouping does not include all significant columns to
consider one row unique.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

I would like to group by fewer columns, but I get an error trying to
compile.
As an example, eleminating the SHIFT column from group by gives the
following error:

Msg 8120, Level 16, State 1, Procedure PWQR_Select_Teardown_15_Day_Summary,
Line 26

Column 'TWQRHISTEARDOWN_HEADER.WQR_TDH_SHIFT_X' is invalid in the select
list because it is not contained in either an aggregate function or the
GROUP BY clause.



"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:HZ6dneA9cvKKurbVnZ2dnUVZ_t_inZ2d@speakeasy.ne t...
> What happens when you add the column WQR_TDH_HEADER_ID to both SELECT and
> GROUP BY? If you get rows you consider non-duplicate show as duplicate,
> then most likely your grouping does not include all significant columns to
> consider one row unique.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

Actually I mean the opposite, you may need to add more columns to both
SELECT/GROUP BY to get correct results.

You do need to have the columns in the SELECT list as part of the GROUP BY
clause.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

I am kind of new to this. Does each column in SELECT require a GROUP BY? I
am getting an error if I don't include all SELECT columns in GROUP BY
columns. I only need to group by around four columns, but need to display
all columns in SELECT.

Thanks for the help.

Charlie

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:OMidnVEIo6nSsrbVnZ2dnUVZ_vudnZ2d@speakeasy.ne t...
> Actually I mean the opposite, you may need to add more columns to both
> SELECT/GROUP BY to get correct results.
>
> You do need to have the columns in the SELECT list as part of the GROUP BY
> clause.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

The logical query processing goes as follows:

1) FROM - get source data
2) WHERE - apply filters to source data
3) GROUP BY - create groups
4) HAVING - apply filters to groups
5) SELECT - select list
6) ORDER BY - define order

Once groups are created in step 3, the following steps can reference only
grouping columns, aggregate functions, or constant expressions. For that
reason all columns listed in SELECT have to be in GROUP BY.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Thanks for the help. That cleared some things up.

Charlie

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:LLmdncRVeesv2LbVnZ2dnUVZ_t7inZ2d@speakeasy.ne t...
> The logical query processing goes as follows:
>
> 1) FROM - get source data
> 2) WHERE - apply filters to source data
> 3) GROUP BY - create groups
> 4) HAVING - apply filters to groups
> 5) SELECT - select list
> 6) ORDER BY - define order
>
> Once groups are created in step 3, the following steps can reference only
> grouping columns, aggregate functions, or constant expressions. For that
> reason all columns listed in SELECT have to be in GROUP BY.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

Plamen Ratchev skrev:
> The logical query processing goes as follows:
>
> 1) FROM - get source data
> 2) WHERE - apply filters to source data
> 3) GROUP BY - create groups
> 4) HAVING - apply filters to groups
> 5) SELECT - select list
> 6) ORDER BY - define order
>
> Once groups are created in step 3, the following steps can reference
> only grouping columns, aggregate functions, or constant expressions. For
> that reason all columns listed in SELECT have to be in GROUP BY.

... or be an aggreate function like sum, min or count

SELECT deptno,count(*),min(salary),sum(salary)
FROM dept
WHERE manager='Joe Q Boss'
GROUP BY deptno
HAVING count(*)>2
ORDER BY min(salary)

Leif

I actually already listed aggregate functions: "...only grouping columns,
aggregate functions, or constant expressions...".

Plamen Ratchev
http://www.SQLStudio.com