Group by on a column with duplicates (string values)

Question

I have to create a MS SQL Query on the table below.

Consider I have following single table:

I want to get the following result by grouping on column1:

How can I accomplish this?

@Ullas Because it doesn't make sense, I don't need that information. And also, it's impossible to show these on one row — Ozkan, May 12 '16 at 12:31
Possible duplicate of [SQL query for finding records where count > 1](http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1) — Tab Alleman, May 12 '16 at 12:44

score 0 · Answer 1 · answered May 12 '16 at 12:32

0

Use COUNT with GROUP BY.

Query

SELECT column1, 'N/A' as column2, 'N/A' as column3,
COUNT(column1) AS column4_amount
FROM your_table_name
GROUP BY column1;

answered May 12 '16 at 12:32

Ullas

11,450
4
33
50

And can I also edit this query so that it only shows the result with 2 or more duplicates? If it only occurs once, then I don't want to show it. – Ozkan May 12 '16 at 12:34
1

@Ozkan use `HAVING count(1) > 1`, Check my answer. – Dhwani May 12 '16 at 12:35

score 0 · Answer 2 · answered May 12 '16 at 12:33

SELECT column1, 'N/A' as column2, 'N/A' as column3, COUNT(1) AS column4_amount
FROM table_name
GROUP BY column1

if you don't need column2 and column3 then try below:

SELECT column1, COUNT(1) AS column4_amount
FROM table_name
GROUP BY column1

Update: As you want results which has more than one occurrence, try this:

SELECT column1, 'N/A' as column2, 'N/A' as column3, COUNT(1) AS column4_amount
FROM table_name
GROUP BY column1
HAVING COUNT(1) > 1

score 0 · Answer 3 · edited Oct 28 '16 at 14:09

0

You should use GROUP BY with column1 as GROUP BY helps you group all the items with the same name.

To count total items under a group of items, from the table with the same name, use the COUNT aggregate function. Now you need to check that each item exists more than once. Use the HAVING clause which takes all the groups that have more than two items.

SELECT column1, 'N/A' as column2, 'N/A' as column3, COUNT(1) AS column4_amount
FROM your_table_name
       GROUP BY column1
       HAVING COUNT(1) > 1

edited Oct 28 '16 at 14:09

fragilewindows

1,394
1
15
26

answered May 12 '16 at 12:47

reza.cse08

5,938
48
39

Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight May 12 '16 at 17:14

Group by on a column with duplicates (string values)

3 Answers3