need to write a query that groups by count of part of field 1

[andreadd]

SQL 2008 R2 / GoldMine Premium 9

hello,

I need to write a query on GoldMine's mailbox table to sift out the marketing emails. Naturally there is nothing that gets flagged in a row that says hey its a marketing email so I need to find another route. a suggestion made by someone was to do a group by count. our marketing pieces go to 50+ people so anything under that is more than likely is some sort of general correspondence.

the field i am thinking of using is called mailbox.ref and it is varchar(100)every single entry that i have seen looks like this:

Fred Jones char(10) Marketing email

so when pasted into excel: Fred Jones Marketing email

so my query needs to look at anything after the char(10) and count those and only list those under a count of 50.

I know this is doable but this is over my skis and am hoping one of you brilliant folks can help me out

 

[bborissov]

SELECT SUBSTRING(mailbox.ref, CHARINDEX(mailbox.ref, SPACE(10))+10,8000) AS eMail,
       COUNT(*) AS Cnt
FROM mailbox
WHERE CHARINDEX(mailbox.ref, '          ') > 0
GROUP BY SUBSTRING(mailbox.ref, CHARINDEX(mailbox.ref, SPACE(10))+10,8000)
HAVING COUNT(*) < 50

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[andreadd]

thanks for this - it got me pointing in the right direction. the spaces turned out to be tab (char(9))