CODEWITHSUNDEEP
sqlsql-server-2014
Mark
Mark

Reputation: 35

Count amount of duplicates of specific value in SQL

I have this table:

ID | mssg
--------
 A | 1
 B | 1
 C | 1
 A | 3
 B | 2
 C | 2
 D | 5
...|...

I would like to count how many distinct messages of each type there are and group them for each correlated ID.

I currently have a working script that successfully counts the total amount of duplicate messages for each ID:

SELECT DISTINCT ID, COUNT(ID) AS mssg_count
FROM my_table
GROUP BY ID
ORDER BY ID

But I want to modify it to count for specific messages for when mssg = '1' or mssg = '2'.

I have tried something like this:

SELECT DISTINCT ID, (SELECT COUNT(mssg)
   WHERE mssg = '1'
   HAVING COUNT(mssg) > 1)
GROUP BY ID
FROM my_table

But I am inexperienced with SQL and get syntax errors.

My expected table would look something like this:

ID | Total number of messages | Amnt of messages where mssg = 1 | mssg = 2..
------------------------------------------------------------------------------
 A |            2             |                 1               |      0
 B |            2             |                 1               |      1
 C |            2             |                 1               |      1
 D |            1             |                 0               |      0
...|..........................|.................................|............

Upvotes: 0

Views: 30

Answers (1)

Patrick
Patrick

Reputation: 334

For your individual column. You can use:

sum(CASE WHEN mssg = '1' THEN 1 ELSE 0 END)

Full SQL:

SELECT
    ID,
    sum(CASE WHEN mssg = '1' THEN 1 ELSE 0 END)
FROM my_table
GROUP BY ID

Upvotes: 1

Related Questions