Member Avatar for RJD1
RJD1 0 Newbie Poster

I have a membership list database table (.MDB MS-Access, accessed via <CFQUERY>) with four email address fields (email1, email 2, email3, email4) - all in same table. (Some folks have multiple email addresses, often from different Email Service Providers - ESPs.)

I need to develop an SQL query (or series of queries) to produce a list of all the ESPs (e.g. gmail.com, comcast.net, etc.) along with a count of how many of each there are, across all four of these fields. As an example, the desired end result output should be a single list that looks something like:

comcast.net (6)
gmail.com (14)
cs.com (1)
hotmail.com (4)
att.net (12)
aol.com (2)
. . . .
etc.

So far, I have successfully used the following to get a list of all ESPs from one of the four fields:

<!--- get list of ESPs (e.g. @comcast.net) --->
<cfquery name="cfqGetESP" datasource="#email_datasource#">
    SELECT MID(email1, INSTR(email1, '@') + 1)
    AS esp
    FROM tblMembers
    WHERE email1 IS NOT NULL
</cfquery>

QUESTION 1: What would be the syntax to have this query get those results across all four fields? (email1, email2, email3, email4)

I then tried the following to generate a list with a count of each distinct ESP:

<!--- count how many total occurrences of EACH individual ESP were found --->
<cfquery name="cfqCountESP" dbtype="query">
    SELECT esp, COUNT(esp) AS instances
    FROM cfqGetESP
    GROUP BY esp
    ORDER BY esp ASC
</cfquery>

<p>TEST DISPLAY<br>
<cfoutput>
<cfloop query="cfqCountESP">
#cfqCountESP.esp#<br>
</cfloop>
</cfoutput>

But, my output results in just a list of distinct ESPs without the count of each:

aol.com
att.net
comcast.net
cs.com
gmail.com
hotmail.com
. . . .
etc.

QUESTION 2: What would be the correct way (added variable?) to display output with the count of each, such as "comcast.net (6)"?

#cfqCountESP.esp# (#cfqCountESP.??????#)

Thanks for your help!

  • 1 Contributor
  • 0 Replies
  • 211 Views

Dani AI

Generated

For — short fixes and a reliable ColdFusion approach.

The reason the counts weren’t showing is simply that the count column from the GROUP BY has to be output explicitly. Print the alias you used on COUNT(...) inside the same query loop (for example, print the column you aliased for the count). Also check the query-of-query actually returned rows (use a dump) and normalize domain text (trim/lowercase) so similar domains aren’t split by case or stray spaces.

An easy, more robust option is to pull the four email columns into CF and do the domain extraction and tally in CFML. That avoids database-specific string functions and gives control for trimming, validation, and optional per-member dedupe. Example workflow (fetch raw columns, extract text after "@", lowercase, count occurrences, then sort by count):

<cfquery name="qM" datasource="#email_datasource#">
  SELECT email1, email2, email3, email4
  FROM tblMembers
  WHERE email1 IS NOT NULL OR email2 IS NOT NULL OR email3 IS NOT NULL OR email4 IS NOT NULL
</cfquery>

<cfset counts = {}>

<cfloop query="qM">
  <cfloop list="email1,email2,email3,email4" index="col">
    <cfset addr = Trim( Evaluate(col) )>
    <cfif Len(addr) AND Find("@", addr)>
      <cfset dom = LCase( ListLast(addr, "@") )>
      <cfif StructKeyExists(counts, dom)>
        <cfset counts[dom] = counts[dom] + 1>
      <cfelse>
        <cfset counts[dom] = 1>
      </cfif>
    </cfif>
  </cfloop>
</cfloop>

<!-- build an array for sorting and output -->
<cfset arr = []>
<cfloop collection="#counts#" item="d">
  <cfset arrayAppend(arr, {domain=d, count=counts[d]})>
</cfloop>
<cfscript>
arraySort(arr, function(a,b){ return b.count - a.count; });
</cfscript>

<cfoutput>
<cfloop array="#arr#" index="it">
#it.domain# (#it.count#)<br>
</cfloop>
</cfoutput>

Notes and gotchas: trim and lowercase domains before counting; ignore entries without an @; decide whether to count every address occurrence or only one hit per member (to count once per member, gather domains per member into a temporary set before adding to the global tally). If a pure-SQL solution is preferred, flatten the four columns with UNION ALL, extract the domain in each branch, then GROUP BY domain in an outer query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.