TSQL Grouping and selecting count of rows from sub query

user_3862378 0
Viewed 49

I have following table

AccountID   Name
1           Foo Bar
2           Jon Dow

AccountID   AddressLine         City
1           123 Test RD         New York
1           456 Test RD         New York
2           Lombard Street      San Francisco
2           Lombard Street      San Francisco

For given AccountID i want to select AddressLine and City.
If the Account has same AddressLine then it should select that AddressLine value else return 'Multiple'.
If the Account has same City then it should select that City value else return 'Multiple'.

So for example for the account ID 1 the query should return 

AccountID  AddressLine City
1          Multiple    New York

Here is SQLFiddle

Below is my query ( Not working). I think the issue is grouping and selecting count from sub squery

SELECT
    A.AccountID,
    CASE
        WHEN T1.CNT = 1
            THEN T1.AddressLine
        ELSE 'Multiple'
    END AS 'Address Line',
    CASE WHEN T2.CNT = 1
        THEN T2.City
    ELSE 'Multiple'
    END AS 'City'   
FROM Accounts A
INNER JOIN
(
    SELECT 
        ad.AccountID,
        COUNT(DISTINCT(ad.AccountID)) AS CNT,
        ad.AddressLine
    FROM Addresses ad   
    GROUP BY ad.AccountID, ad.AddressLine
) T1 ON T1.AccountID = A.AccountID
INNER JOIN
(
    SELECT 
        ad.AccountID,
        COUNT(DISTINCT(ad.AccountID)) AS CNT,
        ad.City
    FROM Addresses ad   
    GROUP BY ad.AccountID, ad.City
) T2
ON T2.AccountID = A.AccountID
WHERE 
    a.AccountID = 1
sql-server-2017 sql-server-2012 tsql sql-server
4 Answers

I think this should do it. I used thestring_agg function to roll up the addresses in the group by since you only want to show the value if there is a single value anyway. I left off the where predicate to show only a single Account, but it's trivial to add it back.

;with dist
as
(
  SELECT DISTINCT
  AccountID
  ,AddressLine
  ,City
  FROM addresses
)
,agg
as
(
SELECT 
  AccountID
  ,COUNT(*) as AddressCount
  ,STRING_AGG(AddressLine, ',') as Address
  ,STRING_AGG(City, ',') as City
FROM dist 
GROUP BY AccountID
)

select 
AccountID
,CASE AddressCount 
  WHEN 0 THEN 'N/A'
  WHEN 1 THEN Address
  ELSE 'Multiple' END as Address
,CASE AddressCount 
  WHEN 0 THEN 'N/A'
  WHEN 1 THEN City
  ELSE 'Multiple' END as City
from agg
user_133130370

Here's another solution using CROSS APPLY

SELECT
    AccountID
,   CASE 
        WHEN x.countOfDistinctAddressLine = 1 AND x.countOfDistinctCity = 1
        THEN x.firstAddressLine
        ELSE 'Multiple'
        END AS AddressLine
,   CASE 
        WHEN x.countOfDistinctAddressLine = 1 AND x.countOfDistinctCity = 1
        THEN x.firstCity
        ELSE 'Multiple'
        END AS City
FROM
    Addresses AS source
CROSS APPLY
    (
        SELECT
            COUNT(DISTINCT AddressLine) AS countOfDistinctAddressLine
        ,   COUNT(DISTINCT City) AS countOfDistinctCity
        ,   MIN(AddressLine) AS firstAddressLine
        ,   MIN(City) AS firstCity
        FROM
            Addresses
        WHERE
            AccountID = source.AccountID
    ) x
GROUP BY
    AccountID
,   x.countOfDistinctAddressLine
,   x.countOfDistinctCity
,   x.firstAddressLine
,   x.firstCity;
user_3939920

Here's a straight-forward example you can run in SSMS:

DECLARE @Accounts TABLE ( AccountID INT, Name VARCHAR(50) );
INSERT INTO @Accounts ( AccountID, Name ) 
    VALUES ( 1, 'Foo Bar' ), ( 2, 'Jon Dow' );

DECLARE @Addresses TABLE ( AccountID INT, AddressLine VARCHAR(50), City VARCHAR(50) );
INSERT INTO @Addresses ( AccountID, AddressLine, City )
    VALUES ( 1, '123 Test Rd', 'New York' ), ( 1, '456 Test Rd', 'New York' ), ( 2, 'Lombard Street', 'San Francisco' ), ( 2, 'Lombard Street', 'San Francisco' );

SELECT
    Accounts.AccountID,
    AddressRecords.AddressLine,
    Addresses.City
FROM @Accounts AS Accounts
INNER JOIN @Addresses AS Addresses
    ON Accounts.AccountID = Addresses.AccountID
OUTER APPLY (

    SELECT CASE
        WHEN ( SELECT COUNT ( DISTINCT ( x.AddressLine ) ) FROM @Addresses AS x WHERE x.AccountID = Accounts.AccountID ) > 1 THEN 'Multiple'
        ELSE ( SELECT DISTINCT AddressLine FROM @Addresses AS x WHERE x.AccountID = Accounts.AccountID )
    END AS AddressLine

) AS AddressRecords
WHERE
    Accounts.AccountID = 1
GROUP BY
    Accounts.AccountID, AddressRecords.AddressLine, Addresses.City;

AccountID 1 RETURNS

+-----------+-------------+----------+
| AccountID | AddressLine |   City   |
+-----------+-------------+----------+
|         1 | Multiple    | New York |
+-----------+-------------+----------+

AccountID 2 RETURNS

+-----------+----------------+---------------+
| AccountID |  AddressLine   |     City      |
+-----------+----------------+---------------+
|         2 | Lombard Street | San Francisco |
+-----------+----------------+---------------+

Of course, this isn't taking into account the possibility of multiple city values.

user_33913340

Might as well add mine to the mix. It's not better than any others, but here it is anyway...

SELECT
    A.AccountID,
    T1.AddressLine,
    T2.City
FROM Accounts A
INNER JOIN
(
    SELECT 
        ad.AccountID,
        CASE WHEN COUNT(DISTINCT(ad.AddressLine)) > 1 THEN 'Multiple' ELSE MIN(ad.AddressLine) END AS AddressLine
    FROM Addresses ad   
    GROUP BY ad.AccountID, ad.city
) T1 ON T1.AccountID = A.AccountID
INNER JOIN
(
    SELECT 
        ad.AccountID,
        CASE WHEN COUNT(DISTINCT(ad.City)) > 1 THEN 'Multiple' ELSE MIN(ad.City) END AS City
    FROM Addresses ad   
    GROUP BY ad.AccountID
) T2
ON T2.AccountID = A.AccountID
WHERE 
    a.AccountID = 1
user_9785280
Related