none
multiple conditional sum sql c# RRS feed

  • Question

  • I wish to query an access data base using two conditions : each condition works on it own but not when put together, can someone suggest what is wrong or missing.

    using .net 4 and c#

       strb.Append(" SELECT  DISTINCT [S_ITEMNO], [S_INVOICEFISCAL], ");
    
                strb.Append(" SUM ( [S_INVOICEQTY]) AS UNITS,");
                strb.Append(" SUM ( [S_INVOICEEXT]) AS DOLLARS ");
                strb.Append(" FROM [TABLE_ORDERS] ");
                strb.Append(" WHERE ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2015.04')");
    
    
                strb.Append(" SUM ( [S_INVOICEQTY]) AS UNITS1,");
                strb.Append(" SUM ( [S_INVOICEEXT]) AS DOLLARS1 ");
                strb.Append(" FROM [TABLE_ORDERS] ");
                strb.Append(" WHERE ([S_INVOICEFISCAL] >='2015.05' AND [S_INVOICEFISCAL] <= '2015.12')");
    
                strb.Append(" GROUP BY [S_ITEMNO],[S_INVOICEFISCAL] ");

    Saturday, October 31, 2015 1:00 PM

Answers

  • SELECT
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEQTY],0)) AS UNITS
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEEXT],0)) AS DOLLARS
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04' AND [S_INVOICEFISCAL] <= '2015.12',[S_INVOICEQTY],0)) AS UNITS1
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04' AND [S_INVOICEFISCAL] <= '2015.12',[S_INVOICEEXT],0)) AS DOLLARS1
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.12',[S_INVOICEQTY],0)) AS UNITS2
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.12',[S_INVOICEEXT],0)) AS DOLLARS2
    FROM 
        [TABLE_ORDERS]
    WHERE 
        ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2016.01')
    GROUP BY 
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]


    Michał

    • Marked as answer by CanAnn Monday, November 2, 2015 12:54 AM
    Sunday, November 1, 2015 8:21 PM

All replies

  • CanAnn,

    You can run 2 separate query or 1 with conditional sum:

    SELECT
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEQTY],0)) AS UNITS
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEEXT],0)) AS DOLLARS
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04',[S_INVOICEQTY],0)) AS UNITS1
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04',[S_INVOICEEXT],0)) AS DOLLARS1
    FROM 
        [TABLE_ORDERS]
    WHERE 
        ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2015.12')
    GROUP BY 
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]



    Michał


    Saturday, October 31, 2015 3:53 PM
  • Thank you - as I am not familiar with this format how would I express this condition?


                strb.Append(" SUM(IIF([S_INVOICEFISCAL] >= '2015.05',[S_INVOICEQTY],0)) AS UNITS2,"); // SHOULD ALSO BE <= '2015.02'
                strb.Append(" SUM(IIF([S_INVOICEFISCAL] >= '2015.05',[S_INVOICEEXT],0)) AS DOLLARS2");// SHOULD ALSO BE <= '2015.12'
    


    Sunday, November 1, 2015 1:45 PM
  • CanAnn,

    From yours first post you sum for this condition:

    strb.Append(" WHERE ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2015.04')");
    strb.Append(" WHERE ([S_INVOICEFISCAL] >='2015.05' AND [S_INVOICEFISCAL] <= '2015.12')");

    so, you need for all S_INVOICEFISCAL between 2015.02 and 2015.12

    Look for my WHERE condition in query:

    WHERE 
        ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2015.12')

    You sum from 2015.2 to 2015.04 and for period 2015.05 to 2015.12 =>

    in scope 2015.02 - 2015-12:

     [S_INVOICEFISCAL] <= '2015.04')  = 2015.02 to 2015.04

     [S_INVOICEFISCAL] > '2015.04')  = 2015.05 to 2015.12

    See IIF Function


    Michał


    Sunday, November 1, 2015 3:28 PM
  • I understand that, now I want to expand the sum(IIF- I have three conditions, and the WHERE has changed.

    1= 2015.02 +2015.03+2015.04

    2= 2015.05 to 2015.12

    3 = 2016.01

    WHERE 
        ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2016.01')

    I export the resulting table to one excel sheet.

      strb.Append(" SUM(IIF([S_INVOICEFISCAL] >= '2015.05',[S_INVOICEQTY],0)) AS UNITS2,"); // SHOULD ALSO BE <= '2015.02'
      strb.Append(" SUM(IIF([S_INVOICEFISCAL] >= '2015.05',[S_INVOICEEXT],0)) AS DOLLARS2");// SHOULD ALSO BE <= '2015.12'


    Sunday, November 1, 2015 8:10 PM
  • SELECT
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEQTY],0)) AS UNITS
        ,SUM(IIF([S_INVOICEFISCAL] <= '2015.04',[S_INVOICEEXT],0)) AS DOLLARS
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04' AND [S_INVOICEFISCAL] <= '2015.12',[S_INVOICEQTY],0)) AS UNITS1
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.04' AND [S_INVOICEFISCAL] <= '2015.12',[S_INVOICEEXT],0)) AS DOLLARS1
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.12',[S_INVOICEQTY],0)) AS UNITS2
        ,SUM(IIF([S_INVOICEFISCAL] > '2015.12',[S_INVOICEEXT],0)) AS DOLLARS2
    FROM 
        [TABLE_ORDERS]
    WHERE 
        ([S_INVOICEFISCAL] >='2015.02' AND [S_INVOICEFISCAL] <= '2016.01')
    GROUP BY 
        [S_ITEMNO]
        ,[S_INVOICEFISCAL]


    Michał

    • Marked as answer by CanAnn Monday, November 2, 2015 12:54 AM
    Sunday, November 1, 2015 8:21 PM
  • Beautiful , my many thanks for your help and patience.

    Monday, November 2, 2015 12:49 AM