CODEWITHSUNDEEP
sql-servercount
Bharath theorare
Bharath theorare

Reputation: 554

Count duplicate values in SQL Server and display as another column

I need a query to obtain following result. The aim is to count the repeated value of a column in A or B or C and put in column C. Also the repeated rows should be truncated or not displayed.

The difficulty is SQL Server takes date as both date and time. So distinct value of date cannot be separated from 'datetime'.

Column A is from one table(say TableA) and Column B is from another table(say TableB).

Table_1: (Before using query)

Column_A  | Column_B |      Column_C
12345678  |  AB      | 2013-08-30 15:06:52.000
12345678  |  AB      | 2013-08-30 15:08:19.000
12345678  |  AB      | 2013-08-30 16:08:19.000
23456789  |  BC      | 2014-06-20 10:08:19.000
23456789  |  BC      | 2014-06-20 12:08:19.000
34567891  |  CD      | 2016-03-20 10:08:19.000

Table_1: (After using query, the table should look like this below)

Column_A  | Column_B |      Column_C           | Column_D
12345678  |  AB      | 2013-08-30 00:00:00.000 | 3
23456789  |  BC      | 2014-06-20 00:00:00.000 | 2
34567891  |  CD      | 2016-03-20 00:00:00.000 | 1

Upvotes: 2

Views: 2389

Answers (1)

Doliveras
Doliveras

Reputation: 1769

All you need is a Group by:

SELECT Column_A, Column_B, count(*) as Column_C
FROM Table_1
GROUP BY Column_A, Column_B

Upvotes: 1

Related Questions