How do I filter a group-by query on the value of a coalesced column?

Here's my Query that does not work because it apparently violates the rules of the HAVING clause:

SELECT
      COALESCE(Publisher.name, Aff开发者_StackOverflow社区iliate.name) AS Publisher
     ,dbo.SumKeys(Item.id) AS ItemIDs
FROM
    Item
    INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
    INNER JOIN Affiliate ON Item.affid = Affiliate.affid
    INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
    INNER JOIN Campaign ON Item.pid = Campaign.pid
    INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
    LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
GROUP BY
     COALESCE(Publisher.name, Affiliate.name)
    ,ItemAccountingStatus.name
    ,CampaignStatus.name    
HAVING (
    ItemAccountingStatus.name='default'
    and CampaignStatus.name='Verified'
    and Publisher='AdsMain LLC' -- THIS BREAKS THE QUERY
)

QUESTION: is there any way to do this?

I doubt if you need a having clause - try it in the where clause as below

SELECT
      COALESCE(Publisher.name, Affiliate.name) AS Publisher
     ,dbo.SumKeys(Item.id) AS ItemIDs
FROM
    Item
    INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
    INNER JOIN Affiliate ON Item.affid = Affiliate.affid
    INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
    INNER JOIN Campaign ON Item.pid = Campaign.pid
    INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
    LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
where 
    ItemAccountingStatus.name='default'
    and CampaignStatus.name='Verified'
    and Publisher='AdsMain LLC' 
GROUP BY
     COALESCE(Publisher.name, Affiliate.name)
    ,ItemAccountingStatus.name
    ,CampaignStatus.name    

...
and COALESCE(Publisher.name, Affiliate.name) ='AdsMain LLC'
...

Column aliases are allowed only in the ORDER BY. You must use the expression (that generates the aliased column) in WHERE, GROUP BY, HAVING etc. You've already used the expression in the GROUP BY though as expected.

Some DBMS allow you to reference column aliases in other places but not SQL Server, which I think makes it more obvious what is going on.

Or you can use a derived table/CTE approach and SELECT from that (as per your self-answer)

As per SELECT on MSDN, "Logical Processing Order of the SELECT statement"

...Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING (gbn: alias not available)
8. SELECT (gbn: aliases generated here)
9. DISTINCT
10. ORDER BY
11. TOP

SELECT * FROM
(
    SELECT
          COALESCE(Publisher.name, Affiliate.name) AS Publisher
         ,dbo.SumKeys(Item.id) AS ItemIDs
    FROM
        Item
        INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
        INNER JOIN Affiliate ON Item.affid = Affiliate.affid
        INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
        INNER JOIN Campaign ON Item.pid = Campaign.pid
        INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
        LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
    GROUP BY
         COALESCE(Publisher.name, Affiliate.name)
        ,ItemAccountingStatus.name
        ,CampaignStatus.name    
    HAVING (
        ItemAccountingStatus.name='default'
        and CampaignStatus.name='Verified'
    )
) A
WHERE A.Publisher='AdsMain LLC'