以下查询为我提供了ALMOST的正确计数，除了它包括具有重复的p.promo_code和Submitid值的行。如何获取此查询以使用DISTINCT p.promo_code，submitid？
我已经尝试过一些在SO上看到的用于单连接查询的解决方案，例如--- sum * COUNT（DISTINCT p.promo_code，Submitid）/ count（*）
没运气。
这是完整的查询：
SELECT
SUM(CASE WHEN ISNULL(p.promo_code)
AND h.account_number = c2.account_number THEN 1 ELSE 0 END) as 'no_match_acct_match',
SUM(CASE WHEN ISNULL(p.promo_code)
AND (h.account_number <> c2.account_number OR ISNULL(c2.account_number)) THEN 1 ELSE 0 END) as 'no_match',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date > @today THEN 1 ELSE 0 END) as 'future_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END) as 'redeemed_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END) as 'buffered_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END) as 'expired_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END) as 'current_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date > @today
AND (r.resolve_status = 0 OR ISNULL(r.resolve_status)) THEN 1 ELSE 0 END) as 'future_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END) as 'redeemed_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END) as 'buffered_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END) as 'expired_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END) as 'current_noname'
FROM helpdesk h
FORCE INDEX (help_status)
LEFT JOIN promo_codes p
ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp
ON p.promo_code = cp.promo_code
LEFT JOIN customers c
ON cp.customer_number = c.customer_number
LEFT JOIN customers c2
ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r
ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL)
AND (ISNULL(p.promo_code) OR ISNULL(r.promo_code))
它运行很快，说明计划看起来不错，但是由于重复数据，目前有2个计数不可用。
任何和所有帮助将不胜感激。
最佳答案
您应该在这些列上使用分组依据。
SELECT
SUM(
CASE WHEN ISNULL(p.promo_code)
AND h.account_number = c2.account_number THEN 1 ELSE 0 END
) as 'no_match_acct_match',
SUM(
CASE WHEN ISNULL(p.promo_code)
AND (
h.account_number <> c2.account_number
OR ISNULL(c2.account_number)
) THEN 1 ELSE 0 END
) as 'no_match',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date > @today THEN 1 ELSE 0 END
) as 'future_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END
) as 'redeemed_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END
) as 'buffered_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END
) as 'expired_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END
) as 'current_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date > @today
AND (
r.resolve_status = 0
OR ISNULL(r.resolve_status)
) THEN 1 ELSE 0 END
) as 'future_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END
) as 'redeemed_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END
) as 'buffered_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END
) as 'expired_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END
) as 'current_noname'
FROM
helpdesk h FORCE INDEX (help_status)
LEFT JOIN promo_codes p ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp ON p.promo_code = cp.promo_code
LEFT JOIN customers c ON cp.customer_number = c.customer_number
LEFT JOIN customers c2 ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (
p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL
)
AND (
ISNULL(p.promo_code)
OR ISNULL(r.promo_code)
)
group by
p.promo_code,
submitid