Sum based on column value with no-null results

Question

Need your help. Having this table:

Airline        WKS        TypePrint   nprints   Eventtime
UX        MAD2AKB503        BTP        1        2018-08-31 09:41:13.360
UX        MAD2AKB503        GPP        1        2018-08-31 09:41:32.723
UX        MAD2AKB503        GPP        1        2018-08-31 09:41:39.700
KLM       MAD2AKB426        GPP        1        2018-08-31 09:46:03.727
KLM       MAD2AKB426        GPP        1        2018-08-28 04:44:22.650
KLM       MAD2AKB426        GPP        1        2018-08-28 04:44:29.000
UX        MAD2AKB497        GPP        1        2018-08-29 06:03:24.517
KLM       MAD2AKB426        GPP        1        2018-08-31 10:10:23.193
KLM       MAD2AKB426        GPP        1        2018-08-31 10:10:30.837
UX        MAD1AKB223        GPP        1        2018-08-05 20:01:57.857

I would like to sum nprints based in typeprint and Airline column by grouping by WKS. I tried with code as follows:

declare @fecha_inicio date='01-01-2018';
declare @fecha_fin date='12-01-2018';
declare @Airline_id varchar(10)='UX'

SELECT wks, 
       @Airline_id, 
       (SELECT Sum (Cast (nprints AS INT)) AS GPP 
        FROM   dw_prints2 B1 
        WHERE  ( B1.airline = @Airline_id ) 
               AND B1.typeprint = 'GPP' 
               AND ( ( B1.eventtime >= @Fecha_Inicio ) 
                     AND ( B1.eventtime <= @Fecha_Fin ) ) 
               AND A.wks = B1.wks) AS GPP, 
       (SELECT Sum (Cast (nprints AS INT)) AS BTP 
        FROM   dw_prints2 C1 
        WHERE  ( C1.airline = @Airline_id ) 
               AND C1.typeprint = 'BTP' 
               AND ( ( C1.eventtime >= @Fecha_Inicio ) 
                     AND ( C1.eventtime <= @Fecha_Fin ) ) 
               AND A.wks = C1.wks) AS BTP 
FROM   dw_prints2 A 
GROUP  BY wks 
ORDER  BY wks

returning:

wks Airline_id GPP BTP MAD1AKB223 UX 1 NULL MAD2AKB426 UX NULL NULL MAD2AKB497 UX 1 NULL MAD2AKB503 UX 2 1

However my intention is not to return WKS when sum is NULL for GPP and BTP. I mean:

wks Airline_id GPP BTP MAD1AKB223 UX 1 NULL MAD2AKB497 UX 1 NULL MAD2AKB503 UX 2 1

thanks in advance for your help.

add a case to where clause or do a sub select and exclude where those are null — Brad, Nov 12 '18 at 15:46
Not the same question (since you didn't mention you tried to use `WHERE GPP is not null` or so), but still the same answers: ['invalid column name' while using the HAVING](https://stackoverflow.com/questions/34741289/invalid-column-name-while-using-the-having). Basically, either wrap the whole select in another, so you can filter by those virtual columns, GPP and BTP, or copy the entire condition to the `having` clause. Given the complexity of the expressions, I would definitely do the first. — GolezTrol, Nov 12 '18 at 15:52

score 0 · Accepted Answer · edited Nov 12 '18 at 16:47

0

No need use subquery, just use conditional aggregations. And add HAVING to filter NULL results

SELECT wks, 
       @Airline_id as Airline,
       Sum ( CASE WHEN typeprint = 'GPP'       
                  THEN Cast (nprints AS INT)
             END) AS GPP, 
       Sum ( CASE WHEN typeprint = 'BTP' 
                  THEN Cast (nprints AS INT)
             END) AS BTP,            
FROM dw_prints2
WHERE Airline = @Airline_id
  AND  eventtime >= @Fecha_Inicio 
  AND  eventtime <= @Fecha_Fin 
GROUP BY wks
HAVING
       Sum ( CASE WHEN typeprint = 'GPP'       
                  THEN Cast (nprints AS INT)
             END) IS NOT NULL            
   OR Sum ( CASE WHEN typeprint = 'BTP' 
                  THEN Cast (nprints AS INT)
             END) IS NOT NULL
ORDER BY wks

edited Nov 12 '18 at 16:47

trilero

5
2

answered Nov 12 '18 at 15:57

Juan Carlos Oropeza

47,252
12
78
118

it doesn't work when BTP or GPP is null. It requieres to have both GPP and BTP. but it's certainly a good start! – trilero Nov 12 '18 at 16:33
Yes, I fixed 20 minutes ago. Change `AND` for `OR` – Juan Carlos Oropeza Nov 12 '18 at 16:42
It's much easier to filter before aggregation like : `where nprints is not null and typeprint in ( 'GPP', BTP)` – dnoeth Nov 12 '18 at 16:57
I will check this possibility @dnoeth for performace as I am planning to move millions of rows... – trilero Nov 12 '18 at 17:07
@dnoeth You are right but only if you have index for those fields, – Juan Carlos Oropeza Nov 12 '18 at 17:28
@JuanCarlosOropeza: Even if there's no index it's less data to be aggregated – dnoeth Nov 12 '18 at 17:30
Not sure about that, I think will require a full table scan anyway – Juan Carlos Oropeza Nov 12 '18 at 17:31

Sum based on column value with no-null results

1 Answers1