CODEWITHSUNDEEP
sqlsql-server
Andre Hsu
Andre Hsu

Reputation: 13

Microsoft SQL Server Error

The following SQL throws error 8120, which says:

Column 'webgroup_message2_archive.opened' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Even though I am not using the offending column as part of the SELECT clause. When I change the subquery 

SELECT webgroup_message2_testTable.message_id

to a list like (111,222,...etc)` it works! So the problem comes from the subquery not compatible with aggregated function? I also made sure the data types of subquery match the test expression. 

SELECT 
  CASE
      WHEN arch.opened = '1' 
      THEN(CASE 
             WHEN (message_id IN (SELECT webgroup_message2_testTable.message_id 
                                  FROM webgroup_message2_testTable)) 
             THEN 'Clicked' ELSE 'Opened' END)
      ELSE 'No activity/response'
  END AS status, count(1)
FROM webgroup_message2_archive AS arch
INNER JOIN webgroup_development AS dev
      ON dev.development_id = arch.HTMLID
WHERE dev.email_tracker_code = 'A4725'
GROUP BY 
  CASE
      WHEN arch.opened= '1' 
      THEN( CASE 
             WHEN (message_id IN (SELECT webgroup_message2_testTable.message_id 
                                  FROM webgroup_message2_testTable)) 
             THEN 'Clicked' ELSE 'Opened' END)
      ELSE 'No activity/response'
  END

Upvotes: 1

Views: 106

Answers (2)

David Nhim
David Nhim

Reputation: 469

If you're trying to get a count of messages by status, then you can just use a left join and get rid of the subquery. Similar to Gordon's answer that beat me by a few minutes, but slightly different in appearance. This is only valid if that is your intention.

SELECT 
  CASE
      WHEN arch.opened = '1' 
      THEN (CASE WHEN test.message_id IS NOT NULL THEN 'Clicked' ELSE 'Opened' END)
      ELSE 'No activity/response'
  END AS status, 
  count(distinct(arch.message_id))
FROM webgroup_message2_archive AS arch
INNER JOIN webgroup_development AS dev
      ON dev.development_id = arch.HTMLID
LEFT JOIN webgroup_message2_testTable AS test
      ON arch.message_id = test.message_id
WHERE dev.email_tracker_code = 'A4725'
GROUP BY 
  CASE
      WHEN arch.opened = '1' 
      THEN (CASE WHEN test.message_id IS NOT NULL THEN 'Clicked' ELSE 'Opened' END)
      ELSE 'No activity/response'
  END
  • not sure if arch.message_id is right or if it should be dev.message_id. You should always try to prefix column names with the table/alias name so that there isn't any question as to where the column comes from.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I don't think you can use subqueries in group by clauses. You can do the same thing by using left join to see if the message is available.

SELECT (CASE WHEN arch.opened= '1' 
             THEN (CASE WHEN ttmessage_id is not null 
                        THEN 'Clicked'
                        ELSE 'Opened'
                   END)
             ELSE 'No activity/response'
        END), count(1)
FROM webgroup_message2_archive arch INNER JOIN
     webgroup_development AS dev
     ON dev.development_id = arch.HTMLID left outer join
     (select distinct tt.message_id as ttmessage_id
      from webgroup_message2_testTable tt
     ) tt
     on message_id = tt.ttmessage_id
WHERE dev.email_tracker_code = 'A4725'
GROUP BY (CASE WHEN arch.opened= '1' 
               THEN (CASE WHEN ttmessage_id is not null 
                          THEN 'Clicked'
                          ELSE 'Opened'
                     END)
               ELSE 'No activity/response'
          END)

Upvotes: 2

Related Questions