DUPLICATE ENTRIES IN T-SQL
up vote
0
down vote
favorite
I have a serious problem here. I got this result from my query.
Please follow this link for the screenshot- I can't upload an image yet:
https://i.stack.imgur.com/MgLJR.png
I tried the code shown here to get rid of the duplicates entries but I get an error
Column 'clients.client_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
My code:
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT
client_name, articles.article_id, article_name,
article_price, entry_number,
entry_number * article_price AS montant,
@BcNumber AS bc_number
FROM
articles
JOIN
entries ON articles.article_id = entries.article_id
JOIN
commandes ON commandes.bc_number = entries.bc_number
JOIN
clients ON clients.client_id = commandes.client_id
WHERE
commandes.bc_number = @BcNumber
GROUP BY
articles.article_id
END
Does someone have a solution?
sql-server tsql edited Nov 11 at 8:41
marc_s
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
add a comment |
up vote
0
down vote
favorite
I have a serious problem here. I got this result from my query.
Please follow this link for the screenshot- I can't upload an image yet:
https://i.stack.imgur.com/MgLJR.png
I tried the code shown here to get rid of the duplicates entries but I get an error
Column 'clients.client_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
My code:
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT
client_name, articles.article_id, article_name,
article_price, entry_number,
entry_number * article_price AS montant,
@BcNumber AS bc_number
FROM
articles
JOIN
entries ON articles.article_id = entries.article_id
JOIN
commandes ON commandes.bc_number = entries.bc_number
JOIN
clients ON clients.client_id = commandes.client_id
WHERE
commandes.bc_number = @BcNumber
GROUP BY
articles.article_id
END
Does someone have a solution?
sql-server tsql edited Nov 11 at 8:41
marc_s
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
You don't have any aggregate function likeSUMorCOUNTin yourSELECTlist - so why are you trying to use aGROUP BYthen??? Doesn't make any sense .....
– marc_s
Nov 11 at 8:43
Are you looking fordistinct?
– Zohar Peled
Nov 11 at 10:07
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a serious problem here. I got this result from my query.
Please follow this link for the screenshot- I can't upload an image yet:
https://i.stack.imgur.com/MgLJR.png
I tried the code shown here to get rid of the duplicates entries but I get an error
Column 'clients.client_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
My code:
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT
client_name, articles.article_id, article_name,
article_price, entry_number,
entry_number * article_price AS montant,
@BcNumber AS bc_number
FROM
articles
JOIN
entries ON articles.article_id = entries.article_id
JOIN
commandes ON commandes.bc_number = entries.bc_number
JOIN
clients ON clients.client_id = commandes.client_id
WHERE
commandes.bc_number = @BcNumber
GROUP BY
articles.article_id
END
Does someone have a solution?
sql-server tsql edited Nov 11 at 8:41
marc_s
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
I have a serious problem here. I got this result from my query.
Please follow this link for the screenshot- I can't upload an image yet:
https://i.stack.imgur.com/MgLJR.png
I tried the code shown here to get rid of the duplicates entries but I get an error
Column 'clients.client_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
My code:
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT
client_name, articles.article_id, article_name,
article_price, entry_number,
entry_number * article_price AS montant,
@BcNumber AS bc_number
FROM
articles
JOIN
entries ON articles.article_id = entries.article_id
JOIN
commandes ON commandes.bc_number = entries.bc_number
JOIN
clients ON clients.client_id = commandes.client_id
WHERE
commandes.bc_number = @BcNumber
GROUP BY
articles.article_id
END
Does someone have a solution?
sql-server tsql sql-server tsql edited Nov 11 at 8:41
marc_s
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
edited Nov 11 at 8:41
marc_s
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
edited Nov 11 at 8:41
marc_s
565k12610921245
edited Nov 11 at 8:41
marc_s
565k12610921245
edited Nov 11 at 8:41
marc_s
565k12610921245
565k12610921245
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
asked Nov 10 at 22:49
Armand Mamitiana Rakotoarisoa
53
53
You don't have any aggregate function likeSUMorCOUNTin yourSELECTlist - so why are you trying to use aGROUP BYthen??? Doesn't make any sense .....
– marc_s
Nov 11 at 8:43
Are you looking fordistinct?
– Zohar Peled
Nov 11 at 10:07
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09
add a comment |
You don't have any aggregate function likeSUMorCOUNTin yourSELECTlist - so why are you trying to use aGROUP BYthen??? Doesn't make any sense .....
– marc_s
Nov 11 at 8:43
Are you looking fordistinct?
– Zohar Peled
Nov 11 at 10:07
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09
You don't have any aggregate function like
SUM or COUNT in your SELECT list - so why are you trying to use a GROUP BY then??? Doesn't make any sense .....– marc_s
Nov 11 at 8:43
You don't have any aggregate function like
SUM or COUNT in your SELECT list - so why are you trying to use a GROUP BY then??? Doesn't make any sense .....– marc_s
Nov 11 at 8:43
Are you looking for
distinct?– Zohar Peled
Nov 11 at 10:07
Are you looking for
distinct?– Zohar Peled
Nov 11 at 10:07
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
You should add all the columns you want to remove the duplicate to the GROUP BY.
Try this (Keep in mind the format of your code, this way you improve readability.):
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT client_name
, articles.article_id
, article_name
, article_price
, entry_number
, entry_number*article_price AS montant
, @BcNumber AS bc_number
FROM articles
JOIN entries on articles.article_id = entries.article_id
JOIN commandes ON commandes.bc_number=entries.bc_number
JOIN clients ON clients.client_id=commandes.client_id
WHERE commandes.bc_number = @BcNumber
GROUP BY client_name
, articles.article_id
, article_name
, article_price
, entry_number
END
Note that the calculated column (montant) and bc_number column doesn't go in the GROUP BY, since you already grouping by article_price and entry_number.
answered Nov 10 at 22:54
Freddy G
25119
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
TheGROUP BYallows you to use aggregate functions (SUM,COUNT,MAX, etc.) in yourSELECT. If you're not going to use any aggregate functions, both are the same.
– Freddy G
Nov 11 at 11:46
If you are not using aggregation, usingdistinctis better thangroup bybecause it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.
– Zohar Peled
Nov 12 at 7:47
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
You should add all the columns you want to remove the duplicate to the GROUP BY.
Try this (Keep in mind the format of your code, this way you improve readability.):
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT client_name
, articles.article_id
, article_name
, article_price
, entry_number
, entry_number*article_price AS montant
, @BcNumber AS bc_number
FROM articles
JOIN entries on articles.article_id = entries.article_id
JOIN commandes ON commandes.bc_number=entries.bc_number
JOIN clients ON clients.client_id=commandes.client_id
WHERE commandes.bc_number = @BcNumber
GROUP BY client_name
, articles.article_id
, article_name
, article_price
, entry_number
END
Note that the calculated column (montant) and bc_number column doesn't go in the GROUP BY, since you already grouping by article_price and entry_number.
answered Nov 10 at 22:54
Freddy G
25119
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
TheGROUP BYallows you to use aggregate functions (SUM,COUNT,MAX, etc.) in yourSELECT. If you're not going to use any aggregate functions, both are the same.
– Freddy G
Nov 11 at 11:46
If you are not using aggregation, usingdistinctis better thangroup bybecause it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.
– Zohar Peled
Nov 12 at 7:47
add a comment |
up vote
0
down vote
accepted
You should add all the columns you want to remove the duplicate to the GROUP BY.
Try this (Keep in mind the format of your code, this way you improve readability.):
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT client_name
, articles.article_id
, article_name
, article_price
, entry_number
, entry_number*article_price AS montant
, @BcNumber AS bc_number
FROM articles
JOIN entries on articles.article_id = entries.article_id
JOIN commandes ON commandes.bc_number=entries.bc_number
JOIN clients ON clients.client_id=commandes.client_id
WHERE commandes.bc_number = @BcNumber
GROUP BY client_name
, articles.article_id
, article_name
, article_price
, entry_number
END
Note that the calculated column (montant) and bc_number column doesn't go in the GROUP BY, since you already grouping by article_price and entry_number.
answered Nov 10 at 22:54
Freddy G
25119
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
TheGROUP BYallows you to use aggregate functions (SUM,COUNT,MAX, etc.) in yourSELECT. If you're not going to use any aggregate functions, both are the same.
– Freddy G
Nov 11 at 11:46
If you are not using aggregation, usingdistinctis better thangroup bybecause it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.
– Zohar Peled
Nov 12 at 7:47
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
You should add all the columns you want to remove the duplicate to the GROUP BY.
Try this (Keep in mind the format of your code, this way you improve readability.):
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT client_name
, articles.article_id
, article_name
, article_price
, entry_number
, entry_number*article_price AS montant
, @BcNumber AS bc_number
FROM articles
JOIN entries on articles.article_id = entries.article_id
JOIN commandes ON commandes.bc_number=entries.bc_number
JOIN clients ON clients.client_id=commandes.client_id
WHERE commandes.bc_number = @BcNumber
GROUP BY client_name
, articles.article_id
, article_name
, article_price
, entry_number
END
Note that the calculated column (montant) and bc_number column doesn't go in the GROUP BY, since you already grouping by article_price and entry_number.
answered Nov 10 at 22:54
Freddy G
25119
You should add all the columns you want to remove the duplicate to the GROUP BY.
Try this (Keep in mind the format of your code, this way you improve readability.):
ALTER PROCEDURE dbo.spGetBcByBcNumber
@BcNumber INT
AS
BEGIN
SELECT client_name
, articles.article_id
, article_name
, article_price
, entry_number
, entry_number*article_price AS montant
, @BcNumber AS bc_number
FROM articles
JOIN entries on articles.article_id = entries.article_id
JOIN commandes ON commandes.bc_number=entries.bc_number
JOIN clients ON clients.client_id=commandes.client_id
WHERE commandes.bc_number = @BcNumber
GROUP BY client_name
, articles.article_id
, article_name
, article_price
, entry_number
END
Note that the calculated column (montant) and bc_number column doesn't go in the GROUP BY, since you already grouping by article_price and entry_number.
answered Nov 10 at 22:54
Freddy G
25119
edited Nov 10 at 23:05
answered Nov 10 at 22:54
Freddy G
25119
answered Nov 10 at 22:54
Freddy G
25119
answered Nov 10 at 22:54
Freddy G
25119
25119
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
TheGROUP BYallows you to use aggregate functions (SUM,COUNT,MAX, etc.) in yourSELECT. If you're not going to use any aggregate functions, both are the same.
– Freddy G
Nov 11 at 11:46
If you are not using aggregation, usingdistinctis better thangroup bybecause it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.
– Zohar Peled
Nov 12 at 7:47
add a comment |
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
TheGROUP BYallows you to use aggregate functions (SUM,COUNT,MAX, etc.) in yourSELECT. If you're not going to use any aggregate functions, both are the same.
– Freddy G
Nov 11 at 11:46
If you are not using aggregation, usingdistinctis better thangroup bybecause it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.
– Zohar Peled
Nov 12 at 7:47
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
It works both with your code and with adding distinct articles.article_id and remove the Group by. So what's the best practice between em??
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:26
The
GROUP BY allows you to use aggregate functions (SUM, COUNT, MAX, etc.) in your SELECT. If you're not going to use any aggregate functions, both are the same.– Freddy G
Nov 11 at 11:46
The
GROUP BY allows you to use aggregate functions (SUM, COUNT, MAX, etc.) in your SELECT. If you're not going to use any aggregate functions, both are the same.– Freddy G
Nov 11 at 11:46
If you are not using aggregation, using
distinct is better than group by because it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.– Zohar Peled
Nov 12 at 7:47
If you are not using aggregation, using
distinct is better than group by because it conveys the intent of the author better meaning it's more readable and maintainable. The execution plan of both queries will very likely be the exactly the same, but if I see a query using group by with no aggregating functions I immediately suspect that this is either a mistake or a query that once had an aggregating function and it was removed, or that the query is incomplete.– Zohar Peled
Nov 12 at 7:47
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244191%2fduplicate-entries-in-t-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You don't have any aggregate function like
SUMorCOUNTin yourSELECTlist - so why are you trying to use aGROUP BYthen??? Doesn't make any sense .....– marc_s
Nov 11 at 8:43
Are you looking for
distinct?– Zohar Peled
Nov 11 at 10:07
Yeah, thanks the distinct works.
– Armand Mamitiana Rakotoarisoa
Nov 11 at 11:21
Tip: Reasons not to use images are here.
– HABO
Nov 11 at 18:09