Count Duplicate Data in Column and Display Once Per Row - SQL

Question

In my table, I'm trying to Count duplicate Food_Name into another column called Duplicate_Value.

SELECT [Food_ID]
      ,[Food_Name]
      ,[Amount]

FROM [Foodie].[dbo].[Food]

Normal Query without Count

I tried counting Duplicate_Values like this, but when I executed the query it gave me no results

SELECT [Food_ID]
  ,[Food_Name]
  ,[Amount]
  ,COUNT(*) as Duplicate_Value
FROM [Foodie].[dbo].[Food]
GROUP BY
   [Food_ID]
  ,[Food_Name]
  ,[Amount]
HAVING 
   COUNT(*) > 1

Desired Output

Shmeeku · Answer 1 · 2018-08-07T23:00:47.120

Your solution is almost there - the problem is that although the value of Food_Name is duplicated, the value of Food_ID is not. Since your GROUP BY clause includes Food_ID, you're going to end up with at least one row for each distinct Food_ID value, and since each of those rows only has a count of 1, it gets filtered out in your HAVING clause.

Here's a small modification to your solution that should work the way you want it to:

SELECT MIN([Food_ID]) AS [Food_ID]
  ,[Food_Name]
  ,[Amount]
  ,COUNT(*) as Duplicate_Value
FROM [Foodie].[dbo].[Food]
GROUP BY
  [Food_Name]
  ,[Amount]
HAVING 
  COUNT(*) > 1

Based on your desired output, I wasn't sure what value you wanted for Food_ID, so you can try other aggregates than MIN().

If the values in the Amount column are also distinct for each row, then you need to remove that column from your GROUP BY clause and use an aggregate function in your SELECT statement as well:

SELECT MIN([Food_ID]) AS [Food_ID]
  ,[Food_Name]
  ,AVG([Amount]) AS [Average_Amount]
  ,COUNT(*) as Duplicate_Value
FROM [Foodie].[dbo].[Food]
GROUP BY
  [Food_Name]
HAVING 
  COUNT(*) > 1

Is the value in the Amount column duplicated? If that value is also distinct for each row, then you'll have to remove that from the GROUP BY as well. — Shmeeku, Aug 07 '18 at 22:58
@taji01, I've edited my answer to include a version that will work with distinct Amount values. Let me know if that works for you! — Shmeeku, Aug 07 '18 at 23:01

score 2 · Answer 2 · answered Aug 07 '18 at 22:53

2

Use aggregation!

select min(food_id) as food_id, food_name, count(*) as duplicate_value
from [Foodie].[dbo].[Food] f
group by food_name;

answered Aug 07 '18 at 22:53

Gordon Linoff

1,242,037
58
646
786

Nice! Simple and Clean but doesn't include amount column is there a reason? – taji01 Aug 07 '18 at 23:03
@taji01 . . . The sample results don't have the amount. – Gordon Linoff Aug 08 '18 at 02:00

D-Shih · Accepted Answer · 2018-08-07T22:37:12.943

You can try to use COUNT and ROW_NUMBER with windows function to make it.

CREATE TABLE T(
  Food_ID INT,
  Food_Name VARCHAR(50)
);

INSERT INTO T VALUES (1,'lettuce');
INSERT INTO T VALUES (2,'lettuce');
INSERT INTO T VALUES (3,'lettuce');
INSERT INTO T VALUES (4,'tomato');
INSERT INTO T VALUES (5,'tomato');
INSERT INTO T VALUES (6,'pick');
INSERT INTO T VALUES (7,'pick');

Query 1:

select t1.[Food_ID]
      ,t1.[Food_Name]
      ,t1.[Amount]
      ,t1.Duplicate_Value 
from(
    SELECT *
      ,COUNT(*) over(partition by Food_Name order by Food_Name) as Duplicate_Value
      ,ROW_NUMBER() over(partition by Food_Name order by Food_ID) as rn
    FROM [Foodie].[dbo].[Food]
) t1
where t1.rn = 1

Results:

| Food_ID | Food_Name | Duplicate_Value | rn |
|---------|-----------|-----------------|----|
|       1 |   lettuce |               3 |  1 |
|       6 |      pick |               2 |  1 |
|       4 |    tomato |               2 |  1 |

Wouldn't it be easier to get the min(Food_ID) and the count(*) per Food_Name? — Steven Stip, Aug 07 '18 at 22:38
@StevenStip can you show me what you mean by min(Food_ID) and the Count(*) — taji01, Aug 07 '18 at 22:50

score 0 · Answer 4 · answered Aug 08 '18 at 04:48

0

SELECT t.Food_Name,MIN(t.Food_ID) AS Food_ID,COUNT(t.Food_ID) AS Duplicate_Value
FROM [Food] AS t
GROUP BY t.Food_Name

answered Aug 08 '18 at 04:48

Deep patel

136
6

While this code snippet may be the solution, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Narendra Jadhav Aug 08 '18 at 05:52

Count Duplicate Data in Column and Display Once Per Row - SQL

Normal Query without Count

Desired Output

4 Answers4

Linked