locked
Question on testing summed fields in where clause RRS feed

  • Question

  • Could someone please explain why these two 'WHERE' clauses work differently

    1.  "WHERE (qty1 + qty2) > 0" 

    2. "WHERE qty1 > 0 OR qty2 > 0"

    when I use "WHERE qty1 > 0 OR qty2 > 0" I get the following row

    cust          grpname                      prod            descrip                                             qty1        qyt2     lastinvdt

    5512969  50# YELLOW MESH      BP173903   50# YELLOW MESH PACK1 BAG       95000      0         1/1/2012 12:00 AM

    When I use "WHERE (qty1 + qty2) > 0"  the row does not appear

    example:

     DECLARE @SumTbl TABLE
        (
          cust NVARCHAR(16) ,
          grpname NVARCHAR(30) ,
          prod NVARCHAR(48) ,
          descrip NVARCHAR(100) ,
          qty1 DECIMAL(10, 2) ,
          qty2 DECIMAL(10, 2) ,
          lastinvdt DATETIME
        )

    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  qty1 > 0
            OR qty2 > 0
            OR s.lastinvdt >= DATEADD(m, -6, CURRENT_TIMESTAMP)
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip

    Thanks

    Dave









    Tuesday, April 10, 2012 8:03 PM

Answers

  • I think that you have problem with NULL values.

    Please remember that NULL plus something is still NULL

    I was able to reproduce similar problem with following script:

     DECLARE @SumTbl TABLE
        (
          cust NVARCHAR(16) ,
          grpname NVARCHAR(30) ,
          prod NVARCHAR(48) ,
          descrip NVARCHAR(100) ,
          qty1 DECIMAL(10, 2) ,
          qty2 DECIMAL(10, 2) ,
          lastinvdt DATETIME
        )
    
    insert into @SumTbl
    select '5512969',  '50# YELLOW MESH',      'BP173903',   '50# YELLOW MESH PACK1 BAG',
           95000,      NULL,         '1/1/2012 12:00 AM'
    
    insert into @SumTbl
    select '5512969',  '50# YELLOW MESH',      'BP173903',   '50# YELLOW MESH PACK1 BAG',
           NULL,      0,         '1/1/2012 12:00 AM'
    
    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  (s.qty1 + s.qty2) > 0
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip
    
    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  s.qty1 > 0 OR s.qty2 > 0
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip

    • Proposed as answer by M0nkeyMaster Wednesday, April 11, 2012 12:32 PM
    • Marked as answer by Naomi N Friday, April 13, 2012 3:43 PM
    Wednesday, April 11, 2012 2:00 AM
  • Hi,

    please check in your table that may be you have this type of data 

    cust	grpname	prod	descrip	qty1	qty2
    1	Gp1	prod1	Desc	1	NULL
    2	Gp2	prod2	Desc	NULL	1
    3	Gp3	prod3	Desc	2	NULL
    4	Gp4	prod4	Desc	NULL	2
    5	Gp5	prod5	Desc	3	NULL
    6	Gp6	prod6	Desc	NULL	3
    7	Gp7	prod7	Desc	4	NULL
    8	Gp8	prod8	Desc	NULL	4
    9	Gp9	prod9	Desc	5	NULL
    10	Gp10	prod10	Desc	NULL	5
    11	Gp11	prod11	Desc	6	NULL
    

    1.  "WHERE (qty1 + qty2) > 0"  

    2. "WHERE qty1 > 0 OR qty2 > 0" ( One of the column Value is null the sum of Qty1+Qty = NULL ) for this you are getting this issue 

    thanks 


    • Marked as answer by Duckkiller53 Friday, April 13, 2012 3:39 PM
    Wednesday, April 11, 2012 12:23 PM

All replies

  • I got this row in both cases:

     DECLARE @SumTbl TABLE
        (
          cust NVARCHAR(16) ,
          grpname NVARCHAR(30) ,
          prod NVARCHAR(48) ,
          descrip NVARCHAR(100) ,
          qty1 DECIMAL(10, 2) ,
          qty2 DECIMAL(10, 2) ,
          lastinvdt DATETIME
        )
    insert into @SumTbl
    select '5512969',  '50# YELLOW MESH',      'BP173903',   '50# YELLOW MESH PACK1 BAG',
           95000,      0,         '1/1/2012 12:00 AM'
    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  (qty1 + qty2) > 0
            OR s.lastinvdt >= DATEADD(m, -6, CURRENT_TIMESTAMP)
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, April 10, 2012 10:15 PM
    Tuesday, April 10, 2012 8:16 PM
  • I ran Naomi's script. I got 1 row in both cases.

    Nonetheless, not a good practice to alias the aggregation the same name as the source column.


    Kalman Toth SQL SERVER & BI TRAINING

    Tuesday, April 10, 2012 8:35 PM
  • What do you want to filter on? The individual qty values, or the totals?

    If you want to filter on the totals, you should but that condition in the
    HAVING clause:

    SELECT s.cust, s.grpname, s.prod, s.descrip,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM    @SumTbl s
     WHERE   s.lastinvdt >= DATEADD(m, -6, CURRENT_TIMESTAMP)
     GROUP BY s.cust, s.grpname, s.prod, s.descrip
     HAVING qty1 > 0 OR qty2 > 0


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 10, 2012 10:10 PM
  • I think that you have problem with NULL values.

    Please remember that NULL plus something is still NULL

    I was able to reproduce similar problem with following script:

     DECLARE @SumTbl TABLE
        (
          cust NVARCHAR(16) ,
          grpname NVARCHAR(30) ,
          prod NVARCHAR(48) ,
          descrip NVARCHAR(100) ,
          qty1 DECIMAL(10, 2) ,
          qty2 DECIMAL(10, 2) ,
          lastinvdt DATETIME
        )
    
    insert into @SumTbl
    select '5512969',  '50# YELLOW MESH',      'BP173903',   '50# YELLOW MESH PACK1 BAG',
           95000,      NULL,         '1/1/2012 12:00 AM'
    
    insert into @SumTbl
    select '5512969',  '50# YELLOW MESH',      'BP173903',   '50# YELLOW MESH PACK1 BAG',
           NULL,      0,         '1/1/2012 12:00 AM'
    
    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  (s.qty1 + s.qty2) > 0
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip
    
    SELECT s.cust ,
                s.grpname ,
                s.prod ,
                s.descrip ,
                SUM(s.qty1) AS qty1,
                SUM(s.qty2) AS qty2 ,
                MAX(s.lastinvdt) AS lastinvdt
     FROM   @SumTbl s
     WHERE  s.qty1 > 0 OR s.qty2 > 0
     GROUP BY s.cust ,
                      s.grpname ,
                      s.prod ,
                      s.descrip

    • Proposed as answer by M0nkeyMaster Wednesday, April 11, 2012 12:32 PM
    • Marked as answer by Naomi N Friday, April 13, 2012 3:43 PM
    Wednesday, April 11, 2012 2:00 AM
  • Hi,

    please check in your table that may be you have this type of data 

    cust	grpname	prod	descrip	qty1	qty2
    1	Gp1	prod1	Desc	1	NULL
    2	Gp2	prod2	Desc	NULL	1
    3	Gp3	prod3	Desc	2	NULL
    4	Gp4	prod4	Desc	NULL	2
    5	Gp5	prod5	Desc	3	NULL
    6	Gp6	prod6	Desc	NULL	3
    7	Gp7	prod7	Desc	4	NULL
    8	Gp8	prod8	Desc	NULL	4
    9	Gp9	prod9	Desc	5	NULL
    10	Gp10	prod10	Desc	NULL	5
    11	Gp11	prod11	Desc	6	NULL
    

    1.  "WHERE (qty1 + qty2) > 0"  

    2. "WHERE qty1 > 0 OR qty2 > 0" ( One of the column Value is null the sum of Qty1+Qty = NULL ) for this you are getting this issue 

    thanks 


    • Marked as answer by Duckkiller53 Friday, April 13, 2012 3:39 PM
    Wednesday, April 11, 2012 12:23 PM
  • Thanks that was the problem. 

    Dave.

    Friday, April 13, 2012 3:39 PM