TSQL to perform aggregation with Exists

My sample data source is following

declare @t1 as table
               (
                   sn int,
                   site varchar(max),
                   cond varchar(max),
                   val int
               )

insert into @t1
   select *
   from
       (values
            (1, 'site1', 'X', 100),
            (2, 'site1', 'Y', 200),
            (3, 'site1', 'Z', 300),
            (1, 'site2', 'A', 100),
            (2, 'site2', 'B', 200),
            (3, 'site2', 'C', 300),
            (1, 'site3', 'X', 100),
            (2, 'site3', 'P', 200),
            (3, 'site3', 'Q', 300),
            (1, 'site4', 'A', 100),
            (2, 'site4', 'Y', 200),
            (3, 'site4', 'Q', 300),
            (1, 'site5', 'E', 100), 
            (1, 'site5', 'E', 1000),
            (2, 'site5', 'F', 200),
            (3, 'site5', 'G', 300)
         ) t (a, b, c, d)

I want SQL to check if there exists any row with cond=X or cond=Y by site and if yes, don’t perform any aggregation; if no, perform a sum of val.

I am currently achieving it by following

Method1

select  
    ax.sn, ax.site, ax.cond,
    sum(ax.revisedVal) as revisedTotal
from
    (select distinct 
         a.sn, a.site, a.cond, a.val, t.one, m.revisedVal
     from 
         @t1 a
     outer apply
         (select * 
          from 
              (select *, 1 as one 
               from @t1 b 
               where b.cond = 'Y' or b.cond = 'X') x 
          where a.site = x.site) t
     outer apply 
         (values (case when t.one = 1 then null else a.val end)) m(revisedVal))ax
group by 
    ax.sn, ax.site, ax.cond
order by 
    ax.site, ax.sn

Method 2

SELECT a.sn,
       a.site,
       a.cond,
       Sum(CASE
             WHEN a.site = x.site THEN a.val
             ELSE NULL
           END) AS revisedVal
FROM   @t1 a
       OUTER apply (SELECT b.site
                    FROM   (SELECT site
                            FROM   @t1
                            WHERE  cond <> 'X'
                                    OR cond <> 'Y'
                            EXCEPT
                            SELECT site
                            FROM   @t1
                            WHERE  cond = 'X'
                                    OR cond = 'Y') b
                    WHERE  a.site = b.site) x
GROUP  BY a.site,
          a.sn,
          a.cond

I was wondering if there is any better way of achieving this.

Edit

Desired Result

| sn | site  | cond | revisedTotal |
|----|-------|------|--------------|
| 1  | site1 | X    | NULL         |
| 2  | site1 | Y    | NULL         |
| 3  | site1 | Z    | NULL         |
| 1  | site2 | A    | 100          |
| 2  | site2 | B    | 200          |
| 3  | site2 | C    | 300          |
| 1  | site3 | X    | NULL         |
| 2  | site3 | P    | NULL         |
| 3  | site3 | Q    | NULL         |
| 1  | site4 | A    | NULL         |
| 2  | site4 | Y    | NULL         |
| 3  | site4 | Q    | NULL         |
| 1  | site5 | E    | 1100         |
| 2  | site5 | F    | 200          |
| 3  | site5 | G    | 300          |

Answer

… fiddle

select ax.sn, ax.site, ax.cond, sum(case when ax.one = 1 then null else ax.val end) as revisedTotal
from
(
select *, max(case when cond in ('Y', 'X') then 1 else 0 end) over(partition by site) as one
from @t1
) as ax
group by ax.sn, ax.site, ax.cond
order by ax.site, ax.sn;

Advertisement

Answer