mysql - 1111(HY000): Invalid use of group function | Not able to use group function in THEN PART OF QUERY

Question

在下面的查询中我无法使用

THEN count(DISTINCT(workflow_link.email_api_response_packets.parent_id))

语法，如有任何帮助，我们将不胜感激。

SELECT workflow_link.email_api_response_packets.subscriber_id
     , count(DISTINCT(workflow_link.email_api_response_packets.parent_id)) as cnt
     , SUM(CASE WHEN (workflow_link.email_api_response_packets.action = 'O'
                      AND workflow_link.email_api_response_packets.parent_id IS NOT NULL
                THEN count(DISTINCT(workflow_link.email_api_response_packets.parent_id))
                ELSE 0
                END)*100/SUM(CASE WHEN (workflow_link.email_api_response_packets.id
                                        AND workflow_link.email_api_response_packets.parent_id IS NULL)
                                  THEN 1
                                  ELSE 0
                                  END) AS percentage
FROM workflow_link.email_api_response_packets
WHERE workflow_link.email_api_response_packets.updated_at >= 1403929223
    AND workflow_link.email_api_response_packets.updated_at <= 1503929479
    AND workflow_link.email_api_response_packets.list_id IN (400)
    AND workflow_link.email_api_response_packets.is_test = 0
GROUP BY workflow_link.email_api_response_packets.subscriber_id
HAVING percentage >= 0

Answer 1

请试试这个。我也删除了所有不必要的括号。

SUM(CASE WHEN workflow_link.email_api_response_packets.action = 'O'
              AND workflow_link.email_api_response_packets.parent_id IS NOT NULL
         THEN count(DISTINCT workflow_link.email_api_response_packets.parent_id)
         ELSE 0 END
    ) * 100
      / SUM(CASE WHEN workflow_link.email_api_response_packets.id 
                      AND workflow_link.email_api_response_packets.parent_id IS NULL
                 THEN 1
                 ELSE 0 END) AS percentage