I am doing a assignment to use case statement and group by with an aggregate-CodePudding

 SELECT count(AccountNumber),
CustomerType = CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL
 THEN 'Store with contact'
    when personid IS NOT NULL AND StoreID IS NULL
          THEN 'store'
        when personid IS NULL AND StoreID IS NOT NULL
        THEN 'Person' 
        ELSE 'Error'
        end
    
    FROM Sales.Customer AS c
 LEFT JOIN Person.Person AS P
ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.Store ON
c.StoreID = Store.BusinessEntityID 
Group by CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL 
THEN concat(store.name,'-',CONCAT(lastname,',',firstname,'',CASE WHEN middleName IS 
   NULL 
      THEN ''  WHEN len(middlename) = 1 THEN concat(middlename,'.') when len(middlename)>1
    then middlename ELSE 'Error'
end))     when personid IS NOT NULL AND StoreID IS NULL
          THEN Store.Name
          when personid IS NULL AND StoreID IS NOT NULL
          THEN CONCAT(lastname,',',firstname,'',CASE WHEN middleName IS NULL 
          THEN '' 
          WHEN len(middlename) = 1 
        THEN concat(middlename,'.') 
        when len(middlename)>1
        then middlename
        ELSE 'Error'
        End

CodePudding user response：

Not clear what that bottom case is doing, but you can either replicate the CASE in the group by or even use a CROSS APPLY to reduce duplication

 SELECT count(AccountNumber)
       ,CustomerType = CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
                            when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
                            when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
                            ELSE 'Error'
                       end
    FROM Sales.Customer AS c
    LEFT JOIN Person.Person AS P ON c.PersonID = p.BusinessEntityID
    LEFT JOIN Sales.Store ON c.StoreID = Store.BusinessEntityID 
 Group By CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
                            when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
                            when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
                            ELSE 'Error'
                       end

Or with a CROSS APPLY

SELECT count(AccountNumber)
       ,CustomerType 
    FROM Sales.Customer AS c
    LEFT JOIN Person.Person AS P ON c.PersonID = p.BusinessEntityID
    LEFT JOIN Sales.Store ON c.StoreID = Store.BusinessEntityID 
    Cross Apply ( values ( CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
                            when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
                            when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
                            ELSE 'Error'
                            end
                           ) ) D(CustomerType)
    Group By CustomerType