How to retrieve a column which is not included in group by

Question

I have tried the CTE and subquery approaches in my query below but it didn't work.

I have two tables with two columns; I would like to use only one column for the join condition, union all, and group by as per the requirement. I would like to fetch the other column without mentioning it among the group by columns. How can achieve this?

SELECT BusnPartClientSuppContractId
    ,DivBranchId
FROM (
    SELECT A.BusnPartClientSuppContractId
        ,A.DivBranchId
    FROM tblBusnPartClientSuppContractDivBranch AS A
    WHERE A.BusnPartClientSuppContractId IN (
            SELECT BusnPartClientSuppContractId
            FROM @BusnpartIdsOfOtherContracts
            )
        AND A.BusnPartClientSuppContractId NOT IN (
            SELECT TOP 1 BusnPartClientSuppContractId
            FROM @SuppClientDivBranch2
            )

    UNION ALL

    SELECT B.BusnPartClientSuppContractId
        ,B.DivBranchId
    FROM @SuppClientDivBranch2 AS B
    ) tmp
GROUP BY BusnPartClientSuppContractId
    ,DivBranchId
HAVING COUNT(*) = 1
ORDER BY DivBranchId

I need BusnPartClientSuppContractId but I dont want to include it in the group by clause.

Answer 1

You can use a window aggregate function in a subquery (derived table or CTE, that's not important):

SELECT BusnPartClientSuppContractId,
       DivBranchId
FROM (
    SELECT BusnPartClientSuppContractId,
           DivBranchId,
           cnt = COUNT(*) OVER (PARTITION BY DivBranchId)
    FROM (
        -- the tmp subquery as it is, no change
        ) tmp
    ) t
WHERE cnt = 1
ORDER BY DivBranchId ;

---

Unrelated to the question but the TOP 1 subquery is not deterministic because it doesn't have an ORDER BY. So the whole query is not deterministic and may yield different results per execution, even if the data don't change.