MYSQL count email country suffix

This count gives an idea of how much of the email data is from what country.
Code:

SELECT SUBSTRING_INDEX(email, '.', -1) as Suffix, count(email) as Total
FROM members
WHERE (email != '' AND email NOT NULL)
GROUP BY Suffix
ORDER BY Total DESC
LIMIT 100;

You can remove the LIMIT so that you get a full count.

Tunneling Remote Desktop over SSH in Windows 7

This was a difficult one because no matter what I tried, I could not get Remote Desktop to work over SSH (PuTTY) from a Windows 7 system. I just kept getting an error message that a Remote Desktop was already open.

After a lot of Red Herring websites http://blog.spencerkellis.net/2010/06/tunneling-remote-desktop-over-ssh-in-windows-7/ gave the answer.

Windows 7 blocks 3389 and 3390 from being used on the loopback interface. Answer was to use 3391 as the localhost port.

Take a look at that guys excellent instructions for setting up PuTTY SSH on your local system to then tunnel MS Remote Desktop connection via an encrytped SSH connection.

Categories: How-To, Microsoft, OS
Tags: Microsoft, Server, copssh, putty, remote desktop, SH, tunnel, Windows 7

MySQL count of Geographical postcode selections, grouped

Here is an example for a query to give you a count of members/customers/recipients within a postcode2 area, listed (group by) the postcode2 area.

What do I mean? I wanted to know how many club members were in different England postcode areas thus:

Quote:   

POSTCODE2 COUNT
CA 34
CB 12
...
HP 22
..
MK 62

use this

Code:   

-- England count grouped by postcode2
SELECT SUBSTRING(postcode,1,2) AS POSTCODE2, COUNT(*) AS COUNT
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'))
GROUP BY SUBSTRING(postcode,1,2);

   

MYSQL dedupe data

If you want to select data from one table, ensuring that it does not (de-duplicate) match another table, I find the following useful.

Code:   

SELECT * FROM data_2
WHERE data_2.email
NOT IN (
SELECT data_1.email
FROM data_1
WHERE data_1.email=data_2.email
);

   
If you have a flat-file, .csv for example and you want to cleanse of duplicate records, you can upload it to a temporary table, setting the field you want to ignore duplicates from, as a UNIQUE data field.

Code:

CREATE TABLE 'example' (
'id' BIGINT(20) NOT NULL AUTO_INCREMENT,
'email' VARCHAR(255) NOT NULL,
'forename' VARCHAR(30) NULL DEFAULT NULL,
'surname' VARCHAR(30) NULL DEFAULT NULL,
PRIMARY KEY ('id'),
INDEX 'Index 2' ('email'),
UNIQUE INDEX 'Index 3' ('email')
)
COLLATE='utf8_general_ci'
ENGINE='InnoDB'
AUTO_INCREMENT=1;

The above example will check and remove any duplicate email addresses it finds during import of the file, thus de-duping the data.

MYSQL select random records

If you need to select <n> number of sample records at random, you could try the following. Though, it can be resource intensive.

Code:   

SELECT *
FROM members
WHERE (email != '' AND email NOT NULL)
ORDER BY RAND() LIMIT 0,100;

   
This example will select 100 records at random.

MYSQL count unique data

Here I had to select unique (none duplicated) email addresses from a table. You would do this if say, you were mailing club members but there are multiple family members with the same email address but, you only want to send an email to them once. Hey, no one likes their Inbox filling up with the same email!

Code:   

SELECT COUNT(DISTINCT email)
FROM members
WHERE (email != "" OR email IS NOT NULL);

   

MYSQL count email domains

In a previous job where clients supplied their own data, I had to be careful how of their email campaign went to a particular ESP/FQDN in one go.

This count gives an idea of how much of the email data is for what domains.

Code:   

SELECT SUBSTRING_INDEX(email, '@', -1) as Domain, count(email) as Total
FROM members
WHERE (email != '' AND email NOT NULL)
GROUP BY Domain
ORDER BY Total DESC
LIMIT 100;