Monday, September 14, 2009

Just the domain

I've done this a couple of times before but found myself having to dig through docs to remember how so thought I'd put up a post for future reference.

I wanted to group some users by their email domain for a query I was running in my PostgreSQL database. I have a users table with an email column. Here's a query I ran:


select count(*) as users,
substring(email from (position('@' in email))+1) as "domain"
from users
group by domain
order by users desc
limit 10;


users | domain
-------+---------------
XXXXX | yahoo.com
XXXXX | gmail.com
XXXXX | hotmail.com
XXXXX | aol.com
XXXXX | comcast.net
XXXXX | msn.com
XXXXX | sbcglobal.net
XXXXX | live.com
XXXXX | verizon.net
XXXXX | ymail.com


This allows me to select the email domain from the email column. Pretty handy.

No comments: