Great feedback. How ever, I still don't get the result I want. This is what I have with the outcome and what I expect:
SMS Tracking view
I created the SMS tracking DE with all the fields from the SMS tracking data view. In the first step I run the following query:
SELECT * FROM [_SMSMessagetracking] WHERE Name = 'SMS notificatie test data view'
The outcome is 1 record as expected because I did 1 sent.
DE trigger data
I have a DE where the data is comming in with some additional fields like: klantnaam, telefoonnummer
Then I run the following query as you suggested (with two more fields):
SELECT DISTINCT sns.klantnaam, Count (*) as [TotalSent], sm.Mobile, sm.Name FROM [Notificaties_SMS_Tracking] sm INNER JOIN [Notificaties_standaard_test] sns on sm.Mobile = sns.telefoonnummer WHERE sns.klantnaam IS NOT NULL AND sm.Name = 'SMS notificatie test data view' GROUP BY sns.klantnaam, sm.Name, sm.Mobile
Like I said in the beginning I did just 1 sent to the 'SMS notificatie test data view' SMS. I did the send to the record with the klantnaam 'Een klantnaam'. It is possible that the phonenumber occurs multiple times in the Notificaties_standaard_test DE because there are different SMS messages (per client (klantnaam) there are multiple messages).