Using ColdFusion query, find number of SQL occurrences of a varchar 'headline' but show only one instance of that headline

Question

This CF query does what I want but I'd like to show only one occurrence of each matching "HEADLINE" that has a count greater than 5. I don't need to display the actual count, just the headline which will be a link using that headline as a url variable. And there must be a more efficient way for my code to work?

<cfquery name="HeadInfo" datasource="certify">
    select headline
    from post
    where deleted = 0
    and headline IS NOT NULL
    order by altid desc
</cfquery>

<cfoutput>#HeadInfo.RecordCount#</cfoutput>

<cfoutput query="HeadInfo">

    <cfquery name="CountInfo" datasource="certify">
        select *
        from post
        where deleted = 0
        and headline = '#HeadInfo.Headline#'
        order by headline desc
    </cfquery>

<cfif CountInfo.RecordCount GT 5>
  #HeadInfo.headline# - Count:#CountInfo.RecordCount#<br>
</cfif>
</cfoutput>

Answer 1

Let's assume you have a MySQL or MariaDB table named post with the following structure:

CREATE TABLE IF NOT EXISTS `post` (
  `altid` int(11) NOT NULL AUTO_INCREMENT,
  `headline` varchar(50) DEFAULT NULL,
  `post` varchar(255) DEFAULT NULL,
  `deleted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`altid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4;

Populated with some test values like these:

INSERT INTO `post` (`altid`, `headline`, `post`, `deleted`) VALUES
    (1, 'headline1', 'post headline1 is foo', 0),
    (2, 'headline1', 'post headline1 is bar', 0),
    (3, 'headline1', 'post headline1 is foobar', 0),
    (4, 'headline1', 'post headline1 is contoso', 0),
    (5, 'headline1', 'post headline1 is contoso foo', 0),
    (6, 'headline1', 'post headline1 is contoso bar', 0),
    (7, 'headline2', 'post headline2 is foo is deleted', 1),
    (8, 'headline2', 'post headline2 is bar', 0),
    (9, 'headline2', 'post headline2 is barFoo', 0),
    (10, 'headline2', 'post headline2 is barFoo contoso', 0),
    (11, 'headline2', 'post headline2 is foo contoso', 0),
    (12, 'headline2', 'post headline2 is contoso bar', 0),
    (13, NULL, 'post headline3 is bar with NULL headline', 0),
    (14, 'headline2', 'post headline2 of another post', 0),
    (15, 'headline2', 'post headline2 of another foobar post', 0),
    (16, 'headline3', 'post headline3 some post for count<5', 0);

You could achieve this even without a JOIN as some have suggested, because it's the same table and this kind of SELECT won't need to have the table JOINED to itself.

In case you need to output the headline recordcount of all headlines (including those < 5), but output only the hits with counts > 5, do it with just one SELECT like follows:

<cfquery name="HeadInfo" datasource="certify">
    SELECT      headline, count( headline ) as headlineCount -- aggregate function count()
    FROM        post
    WHERE       deleted = 0
                and headline IS NOT NULL
    GROUP BY    headline    -- group by for aggregate count() function
    ORDER BY    altid desc
</cfquery>


<cfoutput>
  Total of headlines #HeadInfo.recordcount#<br>
  <cfloop query="HeadInfo">
    <cfif HeadInfo.headlineCount GT 5>
        #HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
    </cfif>
  </cfloop>
</cfoutput>

The output is:

Total of headlines 3
headline2 - Count:7
headline1 - Count:6

But if the recordcount of all headlines isn't needed, then use the aggregate SQL function count() with GROUP BY using the HAVING clause to select only the headlines with count > 5 already in SQL. That will reduce payload inbetween the DB connection with the CF Engine (please see my SQL-Comments inbetween the lines):

<cfquery name="HeadInfo" datasource="certify">
    SELECT      headline, count( headline ) as headlineCount -- aggregate function count()
    FROM        post
    WHERE       deleted = 0
                and headline IS NOT NULL
    GROUP BY    headline    -- group by for aggregate count() function
    HAVING      count( headline ) > 5 -- get headlines with more than 5 posts only (aggregate function count)
    ORDER BY    altid desc
</cfquery>


<cfoutput>
  <cfloop query="HeadInfo">
    #HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
  </cfloop>
</cfoutput>

The output is:

headline2 - Count:7
headline1 - Count:6

Answer 2

You need to do it in one query.

<cfquery name="HeadInfo" datasource="headlines">
SELECT          P.headline
FROM            post as P
    INNER JOIN  (
                    SELECT      headline
                    FROM        post
                    WHERE       deleted = 0
                    GROUP BY    headline
                        HAVING  COUNT(headline) > 5
                ) AS PC
        ON      P.headline = PC.headline
WHERE           P.deleted = 0
    AND         P.headline IS NOT NULL
ORDER BY        altid DESC
</cfquery>

<ul>
    <cfoutput query="HeadInfo">
        <li>#HeadInfo.headline#</li>
    </cfoutput>
</ul>