How can I get the accurate and optimized grouped data by passing expression in partition by statement?

Question

I need to get the correction and a way to group it in a better and efficient way.

What I want is that I need to group the data on the bases of RenterUserId or MainDriverUserId in following cases /scenarios 1). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is not equal to null or empty 2). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is equal to null or empty

Hope so it is now more clear.

please see my query

Select ba.Id, ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,
 ROW_NUMBER() over (partition by ar.RenterUserId,ad.MainDriverUserId order by ar.RenterUserId desc) RowNo ,
 (case when ba.RentalAgreementId <> '' then Count(ba.Id)
 OVER (Partition by (case when ba.RentalAgreementId <> '' then ar.RenterUserId else 0 end)) 
 + Count(ba.Id)
 OVER (Partition by (case when ba.RentalAgreementId <> '' then ad.MainDriverUserId else 0 end))
 else 0 end) RACount,
 (case when ba.RentalAgreementId = '' then Count(ba.Id)
 OVER (Partition by (case when ba.RentalAgreementId = '' then ar.RenterUserId else 0 end))
 + Count(ba.Id)
 OVER (Partition by (case when ba.RentalAgreementId = '' then ad.MainDriverUserId else 0 end))
 else 0 end) ResCount
 from BookingAgreements ba with (nolock)
 left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId
 left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId

See the plan at here

https://www.brentozar.com/pastetheplan/?id=S1kJwEtHi

Answer 1

I believe what you are trying to achieve is actually the grouping of the data, not the counting of the data across an ungrouped dataset. Let me show you what I mean.

If I populate a dataset that I believe matches what you are looking for using the code below...

CREATE TABLE #BookingAgreements
(
    id INT NOT NULL IDENTITY(1, 1)
  , RentalAgreementId INT
);

CREATE TABLE #AgreementRenters
(
    AgreementId INT NOT NULL
  , RenterUserId INT
);

CREATE TABLE #AgreementDrivers
(
    AgreementId INT NOT NULL
  , MainDriverUserId INT
);

WITH x
AS
(
    SELECT 1001 AS id
    UNION ALL
    SELECT x.id + 1
    FROM x
    WHERE x.id < 1021
)
INSERT INTO #BookingAgreements
SELECT x.id
FROM x;

INSERT INTO #AgreementRenters
SELECT ba.id
     , 1000 + ba.RentalAgreementId
FROM #BookingAgreements AS ba
WHERE ba.id % 5 = 0;

INSERT INTO #AgreementDrivers
SELECT ba.id
     , 5000 + ba.RentalAgreementId
FROM #BookingAgreements AS ba
WHERE ba.id % 7 = 0;

UPDATE #BookingAgreements
SET RentalAgreementId = NULL
WHERE id IN ( 1, 2, 3 );

I get this sample result set from running the query below -

SELECT *
FROM #BookingAgreements AS ba
    LEFT JOIN #AgreementRenters AS ar
        ON ar.AgreementId = ba.id
    LEFT JOIN #AgreementDrivers AS ad
        ON ad.AgreementId = ba.id;

id          RentalAgreementId AgreementId RenterUserId AgreementId MainDriverUserId
----------- ----------------- ----------- ------------ ----------- ----------------
1           NULL              NULL        NULL         NULL        NULL
2           NULL              NULL        NULL         NULL        NULL
3           NULL              NULL        NULL         NULL        NULL
4           1004              NULL        NULL         NULL        NULL
5           1005              5           2005         NULL        NULL
6           1006              NULL        NULL         NULL        NULL
7           1007              NULL        NULL         7           6007
8           1008              NULL        NULL         NULL        NULL
9           1009              NULL        NULL         NULL        NULL
10          1010              10          2010         NULL        NULL
11          1011              NULL        NULL         NULL        NULL
12          1012              NULL        NULL         NULL        NULL
13          1013              NULL        NULL         NULL        NULL
14          1014              NULL        NULL         14          6014
15          1015              15          2015         NULL        NULL
16          1016              NULL        NULL         NULL        NULL
17          1017              NULL        NULL         NULL        NULL
18          1018              NULL        NULL         NULL        NULL
19          1019              NULL        NULL         NULL        NULL
20          1020              20          2020         NULL        NULL
21          1021              NULL        NULL         21          6021

If I run your query I get this result set which is not what I believe you want, and if you would like me to explain why these values show up this way in a follow up comment, I will be happy to -

id          RenterUserId MainDriverUserId RentalAgreementId RowNo                RACount     ResCount
----------- ------------ ---------------- ----------------- -------------------- ----------- -----------
1           NULL         NULL             NULL              1                    0           0
2           NULL         NULL             NULL              2                    0           0
3           NULL         NULL             NULL              3                    0           0
4           NULL         NULL             1004              4                    29          0
5           2005         NULL             1005              1                    16          0
6           NULL         NULL             1006              5                    29          0
7           NULL         6007             1007              1                    15          0
8           NULL         NULL             1008              6                    29          0
9           NULL         NULL             1009              7                    29          0
10          2010         NULL             1010              1                    16          0
11          NULL         NULL             1011              8                    29          0
12          NULL         NULL             1012              9                    29          0
13          NULL         NULL             1013              10                   29          0
14          NULL         6014             1014              1                    15          0
15          2015         NULL             1015              1                    16          0
16          NULL         NULL             1016              11                   29          0
17          NULL         NULL             1017              12                   29          0
18          NULL         NULL             1018              13                   29          0
19          NULL         NULL             1019              14                   29          0
20          2020         NULL             1020              1                    16          0
21          NULL         6021             1021              1                    15          0

What I believe you want is actually this -

SELECT COUNT(1) AS ResCount
     , IIF(ar.renteruserid IS NOT NULL OR ad.maindriveruserid IS NOT NULL, 'AGREEMENT NOT NULL', 'AGREEMENT NULL')
FROM #BookingAgreements ba WITH (NOLOCK)
    LEFT JOIN #AgreementRenters ar WITH (NOLOCK)
        ON ar.AgreementId = ba.id
    LEFT JOIN #AgreementDrivers ad WITH (NOLOCK)
        ON ad.AgreementId = ba.id
GROUP BY IIF(ar.renteruserid IS NOT NULL OR ad.maindriveruserid IS NOT NULL, 'AGREEMENT NOT NULL', 'AGREEMENT NULL');

OR

SELECT DISTINCT
       COUNT(1) OVER (PARTITION BY IIF(ar.renteruserid IS NOT NULL OR ad.maindriveruserid IS NOT NULL
                                  , 'AGREEMENT NOT NULL'
                                  , 'AGREEMENT NULL')
                   ) AS ResCount
     , IIF(ar.renteruserid IS NOT NULL OR ad.maindriveruserid IS NOT NULL, 'AGREEMENT NOT NULL', 'AGREEMENT NULL')
FROM #BookingAgreements ba WITH (NOLOCK)
    LEFT JOIN #AgreementRenters ar WITH (NOLOCK)
        ON ar.AgreementId = ba.id
    LEFT JOIN #AgreementDrivers ad WITH (NOLOCK)
        ON ad.AgreementId = ba.id;

Both will do the same exact thing. Based on your asking language -

1). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is not equal to null or empty 2). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is equal to null or empty

This result set fulfills your exact requirement -

ResCount    
----------- ------------------
7           AGREEMENT NOT NULL
14          AGREEMENT NULL