SQL output error when try to find duplicate value in a table

Question

select

count([Item Nbr]) as [Item Nbr],

count([Item Flags]) as [Item Flags],

count(UPC) as UPC,

count([Store Nbr]) as [Store Nbr] ,

count (RetrievalWeek) as RetrievalWeek,

* from tblStoreItemForecast

GROUP BY

[Item Nbr],[Item Flags],UPC,

[Store Nbr], [Week 01 Forecast], [Week 02 Forecast],

[Week 03 Forecast],[Week 04 Forecast],[Week 05 Forecast],

[Week 06 Forecast],[Week 07 Forecast],[Week 08 Forecast],[Week 09 Forecast],

[Week 10 Forecast],[Week 11 Forecast],[Week 12 Forecast],[Week 13 Forecast],

[Week 14 Forecast],[Week 15 Forecast],RetrievalWeek

having (count(*) > 1)

order by RetrievalWeek desc

I got the message error Msg 8120,

Level 16, State 1, Line 76

Column 'tblStoreItemForecast.Week 16 Forecast' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 209, Level 16, State 1, Line 91

Ambiguous column name 'RetrievalWeek'.

Can anyone help me why this happen.

I tried another method

if i run the below code it gives me no result as i am using record no (unique key) here

select

[Item Nbr],[Item Flags],UPC,[Store Nbr],RetrievalWeek,recordno,COUNT(*)

from tblStoreItemForecast

GROUP BY

[Item Nbr],[Item Flags],UPC,[Store Nbr],

RetrievalWeek,recordno

having

count(*) > 1

order by RetrievalWeek desc

but if I run the below code without using record no as it is unique here it gives result

select

[Item Nbr],[Item Flags],UPC,[Store Nbr],RetrievalWeek,COUNT(*)

from tblStoreItemForecast

GROUP BY

[Item Nbr],[Item Flags],UPC,[Store Nbr],

RetrievalWeek

having

count(*) > 1

order by RetrievalWeek desc

i want to count the duplicate by using record no how to write the can not figure out i also use inner join but getting error space in the log can not be reused. Is there any solution?

This looks like sql server, not mysql which you tagged. The error is also pretty clear, what don't you understand about it? — HoneyBadger
– HoneyBadger, Commented May 31, 2022 at 12:10
I've removed the conflicting tags; edit your question to retag appropriately. — Thom A
– Thom A ♦, Commented May 31, 2022 at 12:11
Why not use a windowed count then? Aggregating and grouping on the same columns is always the sign of a flaw. — Thom A
– Thom A ♦, Commented May 31, 2022 at 12:31
To be honest it looks like you have a bit of a design flaw here. You have violated 1NF by using repeating groups. [Week 01 Forecast] etc. You have also made it more difficult on yourself by putting spaces in your column names. The grouping by every column sort of suggest your table is also lacking a primary key. — Sean Lange
– Sean Lange, Commented May 31, 2022 at 13:09

user19231494 · Accepted Answer · 2022-05-31 12:38:51Z

0

You have an output column alias with the same name as a table column. Try adding an underscore to differentiate as below.

select

count([Item Nbr]) as [Item Nbr],

count([Item Flags]) as [Item Flags],

count(UPC) as UPC,

count([Store Nbr]) as [Store Nbr] ,

count (RetrievalWeek) as Retrieval_Week,

* from tblStoreItemForecast

GROUP BY

[Item Nbr],[Item Flags],UPC,

[Store Nbr], [Week 01 Forecast], [Week 02 Forecast],

[Week 03 Forecast],[Week 04 Forecast],[Week 05 Forecast],

[Week 06 Forecast],[Week 07 Forecast],[Week 08 Forecast],[Week 09 Forecast],

[Week 10 Forecast],[Week 11 Forecast],[Week 12 Forecast],[Week 13 Forecast],

[Week 14 Forecast],[Week 15 Forecast],RetrievalWeek

having (count(*) > 1)

order by RetrievalWeek desc

answered May 31, 2022 at 12:38

user19231494

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL output error when try to find duplicate value in a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related