CODEWITHSUNDEEP
sqlsql-servert-sql
smpa01
smpa01

Reputation: 4282

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          |

Upvotes: 1

Views: 113

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This sounds like a having clause on an aggregation query:

select site, sum(val)
from @t t
group by site
having sum(case when cond = X or cond = Y then 1 else 0 end) = 0;

EDIT:

with agg as (
      select site, sum(val) as val
      from @t t
      group by site
      having sum(case when cond = X or cond = Y then 1 else 0 end) = 0
     )
select site, val
from agg
union all
select site, val
from @t t
where not exists (select 1 from agg where agg.site = t.site);

Upvotes: 1

lptr
lptr

Reputation: 6788

… 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;

Upvotes: 2

Luuk
Luuk

Reputation: 14920

This:

SELECT t1.site, SUM(t1.val)
from @t1 t1
left join @t1 t2 on t2.site=t1.site and t2.cond in ('X','Y')
where t2.site is null
group by t1.site;

Produces the sum for all values of val, per site, where there does not exist a val with the value 'X' or 'Y'.

see: DBFIDDLE

or, with the other columns added:

SELECT t1.sn, t1.site, t1.cond, SUM(t1.val)
from @t1 t1
left join @t1 t2 on t2.site=t1.site and t2.cond in ('X','Y')
where t2.site is null
group by t1.sn, t1.site, t1.cond;

Upvotes: 0

Related Questions