Count of occurrences per row ignoring the duplicates

Question

Basically, I want to know the SQL Equivalent of Excel's COUNTIF(Range,Criteria)

I want this Output

CALL ID |  Name  | Phone Number | # of Occurrences    
  0001  | Name1  |  0000000000  |        2    
  0002  | Name2  |  1111111111  |        1    
  0003  | Name1  |  0000000000  |        2    
  0004  | Name3  |  2222222222  |        2    
  0005  | Name5  |  4444444444  |        1    
  0006  | Name4  |  3333333333  |        1    
  0007  | Name3  |  2222222222  |        2

NOT This Output

CALL ID |  Name  | Phone Number | # of Occurrences    
  0001  | Name1  |  0000000000  |        2    
  0002  | Name2  |  1111111111  |        1    
  0004  | Name3  |  2222222222  |        2    
  0005  | Name5  |  4444444444  |        1    
  0006  | Name4  |  3333333333  |        1

This is a simplified table. This is my complex table and this is the result Result

What i wanted is to avoid having 2 Appointments, instead, list them in separate rows so that the appointment is only 1

Here is the query

SELECT
a."Call ID" as "Call ID",
CONCAT(a.Campaign, '-', a."CONTACT ID") as IDENTIFIER,
REPLACE(a."Campaign",'CW-','') as "Campaign",
a."DNIS" as "DNIS",
a."CONTACT ID" as "CONTACT ID",
a."first_name" as "first_name",
a."last_name" as "last_name",
a."city" as "city",
a."street" as "street",
a."zip" as "zip",
a."state" as "state",
a."number2" as "number2",
a."number1" as "number1",
a."Customer ID - real" as "Customer ID - real",
COUNT(a."Call ID") as "Dial Attempts"
FROM  "Five9 Calls" a 
group by 
CONCAT(a.Campaign, '-', a."CONTACT ID"),
REPLACE(a."Campaign",'CW-',''),
a."CONTACT ID",
a."first_name",
a."last_name",
a."city",
a."street",
a."zip",
a."state",
a."number2",
a."number1"

Please include attempted solutions, why they didn't work, and the expected results. — Kermit, Aug 19 '13 at 19:52
my attempted solutions are too long, i just simplified it to this one. let me edit — Christian Joy, Aug 19 '13 at 20:04

score 1 · Accepted Answer · answered Aug 19 '13 at 19:55

1

Declan's answer gives you the number of names linked to each phone number (one row per phone number).

If it's important for you to keep the same number of row (i.e. the same layout you've described in your question), you can use an OVER clause.

SELECT NAME, [PHONE NUMBER], COUNT(NAME) OVER (PARTITION BY [PHONE NUMBER]) AS OCCUR
FROM YOURTABLE

With OVER there's no need for a GROUP BY.

answered Aug 19 '13 at 19:55

jods

4,581
16
20

might as well try this. the other answer is also correct but at some point, that is not the output i wanted. because it still groups it by phone number. I actually have a very long query, but I just simplified it to this. – Christian Joy Aug 19 '13 at 20:03
I have tried it, with the code above (I updated the post), and replace it with this: COUNT(a."Call ID") OVER (PARTITION BY CONCAT(a.Campaign, '-', a."CONTACT ID"),REPLACE(a."Campaign",'CW-',''),a."CONTACT ID",a."first_name",a."last_name",a."city",a."street",a."zip",a."state",a."number2",a."number1") as "Dial Attempts" removing the group by and it just returned one row – Christian Joy Aug 19 '13 at 20:15
yeah, real code is always more hairy than concepts ;) Did it work? – jods Aug 19 '13 at 20:19
sorry. i was so noob it is my first time at stackoverflow, so i dunno how to post properly... it worked tho but it returned one row :( can you see something wrong with this? – Christian Joy Aug 19 '13 at 20:20
`OVER()` analytic functions never remove rows. If there's something missing it has to be because of a `WHERE` or `JOIN` or maybe `GROUP BY`. – jods Aug 19 '13 at 20:24
ok! at least i have learned a new function ;) This might be okay, I'll find the logical error myself. Thanks a bunch jods! – Christian Joy Aug 19 '13 at 20:27

score 0 · Answer 2 · answered Aug 19 '13 at 19:50

0

If you need a count of the occurance of each phone number, which is what I can glean from your data, you would use the following.

SELECT [PHONE NUMBER],COUNT(NAME) AS OCCUR
FROM YOURTABLE
GROUP BY [PHONE NUMBER]

answered Aug 19 '13 at 19:50

Declan_K

6,726
2
19
30

But it only returns Distinct Values. I want it to return ALL the values. That is the exact output I wanted. – Christian Joy Aug 19 '13 at 19:53

score 0 · Answer 3 · answered Aug 19 '13 at 19:56

0

select call_id, name, phone_number, count() over (partition by name, phone_id order by name asc, phone_id asc) occur
From yourtable

answered Aug 19 '13 at 19:56

bonsvr

2,262
5
22
33

Count of occurrences per row ignoring the duplicates

3 Answers3