CODEWITHSUNDEEP
sqldistinctpostgresql-9.1
dwarner
dwarner

Reputation: 7

SQL selecting distinct values like other values

So I have a table that is automatically generated by an instant messenger application and I am looking for all messages sent by all users. Each conversation has a "to" column the problem is that it appends a short connection string on the end. so the to column looks like: username@company.com/id 1111

I am looking to get all distinct usernames and the number of times they appear. Any suggestions are appreciated. I am using PostgreSQL 9.1.1

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

The problem is parsing the string. This depends highly on the dialect of SQL. Here is an approach using MySQL:

select substring_index(`to`, '/', 1) as username, count(*)
from t
group by substring_index(`to`, '/', 1);

In most other SQL dialects, the logic would be slightly different. In SQL Server:

select left("to", charindex('/', "to") - 1) as username, count(*)
from t
group by left("to", charindex('/', "to") - 1);

In other databases, the equivalent of charindex() might be instr() or position().

EDIT:

I originally miinterpreted the "username" as being the entire email address. Barmar has pointed out that the formatting in the question really suggests the part before the @. Having just the user name portion of the email, without the domain, seems dangerous. But, the above works, just by substituting '@' for '/':

select substring_index(`to`, '@', 1) as username, count(*)
from t
group by substring_index(`to`, '@', 1);

select left("to", charindex('@', "to") - 1) as username, count(*)
from t
group by left("to", charindex('@', "to") - 1);

Upvotes: 5

Paulwin
Paulwin

Reputation: 93

Is there any commonality/shared characteristic of this 'connection string' ?

If so, then is just a question of creating the string manipulation to help you disregard it in your SQL query.

Without knowing the pattern of the connection string, would be hard to supply an answer.

Upvotes: 0

Related Questions