Querying aggregate columns in a SQL Server SELECT statement

I have a SQL Server query which runs just fine -- until I add a computed column to the SELECT statement. Then I get an odd SQL Server error.

Here's the SQL:

SELECT
    outmail_.MessageID_, 
    CONVERT(VARCHAR(10),outmail_.Created_,120) AS 'Issue', 
    lyrReportSummaryData.mailed, 
    lyrReportSummaryData.successes, 
    COUNT(*) AS 'opens',
    COUNT(DISTINCT clicktracking_.MemberID_) AS 'unique_opens', 
    convert(decimal(3,1),((convert(float,[unique_opens]))/[successes]) * 100) AS 'Rate'
FROM 
    outmail_ 
RIGHT JOIN 
    clicktracking_ ON clicktracking_.MessageID_ = outmail_.MessageID_
RIGHT JOIN 
    lyrReportSummaryData ON lyrReportSummaryData.id = clicktracking_.MessageID_ 
GROUP BY  
    outmail_.MessageID_, CONVERT(VARCHAR(10), outmail_.Created_,120), 
    lyrReportSummaryData.mailed, lyrReportSummaryData.successes

The problem is the line beginning with the convert(decimal ... When it is included, I get the following error:

Error 8120: Column 'lyrReportSummaryData.unique_opens' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm not sure how to resolve the error since I don't know how to use it in a GROUP BY clause (and it doesn't seem that I should need to do so).

Any suggestions for how to proceed? Thanks.

1 Answer

  1. Oliver- Reply

    2019-11-16

    I'm sure someone with better DBA skills than me can point out a more efficient way of doing this, but...

    If you perform the bulk of your query as an sub-query, you can then do the calculations on the result of your sub-query:

     SELECT 
        MessageID_, 
        Issue, 
        mailed, 
        successes, 
        opens, 
        unique_opens, 
        convert(decimal(3,1),((convert(float,[unique_opens]))/[successes]) * 100) AS 'Rate'
    
     FROM 
     (SELECT
          outmail_.MessageID_, 
          CONVERT(VARCHAR(10),outmail_.Created_,120) AS 'Issue', 
          lyrReportSummaryData.mailed, 
          lyrReportSummaryData.successes, 
          COUNT(*) AS 'opens',
          COUNT(DISTINCT clicktracking_.MemberID_) AS 'unique_opens'
    
    
        FROM outmail_ 
        RIGHT JOIN clicktracking_ ON clicktracking_.MessageID_ = outmail_.MessageID_
        RIGHT JOIN lyrReportSummaryData ON lyrReportSummaryData.id = clicktracking_.MessageID_ 
    
        GROUP BY  outmail_.MessageID_, CONVERT(VARCHAR(10), outmail_.Created_,120), lyrReportSummaryData.mailed, lyrReportSummaryData.successes
       ) subquery /* was 'g' */
    

    Effectively what this does is runs the grouping, and then based on that, does the calculation afterwards.

    Subqueries must be given an alias (in this instance 'subquery') - even if you don't use that alias name.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>