CODEWITHSUNDEEP
sqlsum
sanchoniathon
sanchoniathon

Reputation: 73

How to calculate the sum in SQL

Quick question : How could i change the WHERE clause in order to have all those metrics and then compare it > 0

instead of having to make (d.metric1 > 0 or d.metric2 > 0 or d.metric3 > 0 or d.metric4 > 0 or d.metric5 > 0)

SELECT  a.No as NoRB, 
    a.Account# as Account#RB,
    b.Account# as Account#2,
    b.No as No2,
    b.type_cd as type_cd2,
    b.EffectiveDate as EffectiveDate2,       
    b.ExpirationDate as ExpirationDate2,
    b.EntranceDate as EntranceDate2,
    c.postalCode as postal_code2,
    d.No, 
    d.ExpirationDate, 
    d.EntranceDate, 
    d.metric1, 
    d.metric2, 
    d.metric3, 
    d.metric4, 
    d.metric5, 
    d.metric6, 
    d.metric7, 
    d.metric8, 
    d.metric9, 
    d.metric10, 
    d.metric11
FROM DB.CarInsurance a INNER JOIN
     DB.CarInsurance b
     ON a.No = b.No INNER JOIN 
     2ndDB.CarInsuranceINT c
     ON a.Account# = c.Account# INNER JOIN
     2ndDB.CarDriver d 
     ON a.No = d.No
WHERE a.type_cd in ('OcPr','OcNotPr') and
      b.type_cd = 'Pr' and
      a.DateDelivery >= '2020-02-01 00:00:00' and
      (d.metric1 > 0 or d.metric2 > 0 or d.metric3 > 0 or d.metric4 > 0 or d.metric5 > 0)
ORDER BY a.No, a.Account#, a.ExpirationDate, a.EntranceDate , a.EffectiveDate;

Upvotes: 0

Views: 45

Answers (2)

dnoeth
dnoeth

Reputation: 60472

Check if your DBMS supports this Standard SQL syntax:

AND 0 < ANY (d.metric1, d.metric2, d.metric3, d.metric4, d.metric5)

or supports GREATEST:

AND GREATEST(d.metric1, d.metric2, d.metric3, d.metric4, d.metric5) > 0

Edit:

As Gordon mentioned, GREATEST/LEAST will not work correctly for NULLs (at least in the DBMSes I know), because a single NULL yields a NULL result. And fixing it using COALESCE is more complicated than the original ORed conditions.

Upvotes: 1

ddlab
ddlab

Reputation: 930

You could just add the values and compare the sum

AND (d.metric1 + d.metric2 + d.metric3 + d.metric4 + d.metric5) > 0

Or if your SQL-Server supports IIF you can try to put the comparision into the select and only the result in the WHERE clause

SELECT IIF((d.metric1 + d.metric2 + d.metric3 + d.metric4 + d.metric5) > 0, 1, 0) as go,
...
FROM ...
WHERE go = 1 AND...

Or a CASE statement in SELECT like

SELECT CASE
            WHEN (d.metric1 + d.metric2 + d.metric3 + d.metric4 + d.metric5) > 0
               THEN 1
               ELSE 0
       END as go
...
FROM...
WHERE go = 1
AND...

Upvotes: 0

Related Questions