none
SUM Statement, Adding Values From Multiple Files/Columns and IGNORING Nulls

    Question

  • I've got a strange task.  I currently have 6 separate database files of identical schema but different content.  The relevent columns are custID, custPay, custRec.  I need to total each of those three tables, for all 6 of the files.

    Something like "SELECT custID, SUM(all the stores custPay), SUM(all 6 files custREC)     and if it's possible, a total of the difference between those two columns.  

    My problem is that the SUM statement will do nothing after it encounters a null and just return whatever value it stopped at, making my final numbers wrong if there were more entries after that null.

     I tried SUM(ISNULL(custPay, 0)) (not worried about syntax) and it told me too many arguments.   Each file is about 10k records.   I'm using C# 2010, working on .DBF IV files, with the Visual FoxPro for OLEDB drivers, on .NET 2.0 (so I don't think I can use LINQ).

    Thanks in advance for any help, and if I have to explain something differently just let me know!


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    • Moved by Vicky SongMicrosoft employee Thursday, April 12, 2012 6:14 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Wednesday, April 11, 2012 6:52 PM

Answers

  • Is that a downloadable file?  I'm not getting anything conclusive.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    No - what it is saying is that it does not understand coalesce and is assuming it is an external program.   Try NVL - that seems to be the foxpro version of coalesce

    Chuck

    • Marked as answer by psifreak Thursday, April 12, 2012 2:53 PM
    Thursday, April 12, 2012 2:38 PM

All replies

  • Hello psifreak,

    I am moving your case to the T-SQL forum so that you can get better support there. In addition, please offer us more information (table structure, testing data, and T-SQL query) which will be helpful for further trouble-shooting.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, April 12, 2012 6:14 AM
  • Maybe the IIF Function can help? 

    IIF(ISNULL(theexpression),0,theexpression)

    Thursday, April 12, 2012 8:47 AM
  • Thanks for the pointer and the code chunk.  Tried it joscion, it was already claiming too many arguments just using ISNULL, 0 on the one table.

    OK, in folder C:\DBFs, are 6 dbase 4 files, cust01, cust02, and so on.   In each of these is the columns, custID, decimalColumn1, decimalColumn2.  I need a finalized report on these, foreach customer it has to add the totals of column1, and the totals of column2, and outputs something like

    custID     sum(col1)    sum(col2)    sum(col1 - col2)

    cust01      12.00          10.00           2.00

    cust 02      10.00          10.00          0.00

    But it has to do this as a total for all 6 files in these 3 columns.  I'm having problems with the query syntax, it's either erroring out on too many arguments, for some reason on the first use of ISNULL, 0 if I have more than one file in the query.  Anybody know how to either stack multiple queries or shrink one huge one like that?  Without the ISNULL it won't give accurate reports as yet, it stops reading at the first one.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 12:20 PM
  • Try Coalesce instead of ISNULL.  ISNULL in SQL Server is a 2 argument coalesce, in most every other language it is a boolean function that returns true if the argument is NULL - coalesce is ANSI and more likely to be supported by DBase.

    SUM(Coalesce(custPay, 0)) 


    Chuck

    Thursday, April 12, 2012 12:24 PM
  • EDIT ** VS is crashing out, claiming to have an unrecognized command or keyword

    SELECT custlist.supplier, SUM(Coalesce(cust01.ytdpur, 0)) AS store01, SUM(Coalesce(cust02.ytdpur, 0)) AS store02, SUM(Coalesce(cust03.ytdpur, 0)) AS store03, SUM(Coalesce(cust04.ytdpur, 0)) AS store04, SUM(Coalesce(cust05.ytdpur, 0)) AS store05 FROM ((((custlist LEFT JOIN cust01 ON custlist.custID = cust01.custID) LEFT JOIN cust02 ON custlist.custID = cust02.custID) LEFT JOIN cust03 ON custlist.custID = cust03.custID) LEFT JOIN cust04 ON custlist.custID = cust04.custID) LEFT JOIN cust05 ON custlist.custID = cust05.custID WHERE ((cust01.custID)=custlist.custID) OR ((cust02.custID)=custlist.custID) OR ((cust03.custID)=custlist.custID) OR ((cust04.custID)=custlist.custID) OR ((cust05.custID)=custlist.custID)) GROUP BY custlist.custID


    In a nutshell, the only way this thing comes close to working is if each of those stores is queried into separate columns, I need to add the sums of cust01-cust05 ytdpur into a GrandTotal column, and cust01-cust05 ytdpay into a Grand Total column.  Then a column for the difference.  I use custlist as an index, it's only column is custID.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    • Edited by psifreak Thursday, April 12, 2012 12:55 PM
    Thursday, April 12, 2012 12:49 PM
  • Why do you have a seperate table for each store? 


    Chuck




    Thursday, April 12, 2012 12:58 PM
  • First simplify your query:

    SELECT custlist.supplier, 
    SUM(Coalesce(cust01.ytdpur, 0)) AS store01, 
    SUM(Coalesce(cust02.ytdpur, 0)) AS store02, 
    SUM(Coalesce(cust03.ytdpur, 0)) AS store03, 
    SUM(Coalesce(cust04.ytdpur, 0)) AS store04, 
    SUM(Coalesce(cust05.ytdpur, 0)) AS store05 
    FROM custlist 
    LEFT JOIN cust01 ON custlist.custID = cust01.custID  
    LEFT JOIN cust02 ON custlist.custID = cust02.custID 
    LEFT JOIN cust03 ON custlist.custID = cust03.custID 
    LEFT JOIN cust04 ON custlist.custID = cust04.custID 
    LEFT JOIN cust05 ON custlist.custID = cust05.custID 
    
    GROUP BY custlist.custID


    Chuck

    Thursday, April 12, 2012 1:07 PM
  • The system has been around longer than I've been alive, and it's creator/caretaker is pretty protective.  And not just separate tables, separate files.   I'm open to suggestion on this, not sure where to start, so if it starts with a script to merge them, what's a good skeleton code?

    **EDIT   Just saw the other post jo, IF(ISNULL(theexpression),0,theexpression). Does this one do SUM, or would it be IF(SUM(ISNULL ?


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    • Edited by psifreak Thursday, April 12, 2012 1:26 PM
    Thursday, April 12, 2012 1:17 PM
  • If you can create views in Dbase - I would start with a view that just combines all 5 stores and query that.  Assuming that there are only 5 stores - that idea falls apart if you are talking 20's or hundreds.

    Select 'store 1', field1,field2,field3 From cust01

    UNION ALL

    Select 'store 2', field1,field2,field3 From cust02

    UNION ALL

    Select 'store 3', field1,field2,field3 From cust03

    UNION ALL

    Select 'store 4', field1,field2,field3 From cust04

    UNION ALL

    Select 'store 5', field1,field2,field3 From cust05


    Chuck

    Thursday, April 12, 2012 1:22 PM
  • The system has been around longer than I've been alive, and it's creator/caretaker is pretty protective.  And not just separate tables, separate files.   I'm open to suggestion on this, not sure where to start, so if it starts with a script to merge them, what's a good skeleton code?

    **EDIT   Just saw the other post jo, IF(ISNULL(theexpression),0,theexpression). Does this one do SUM, or would it be IF(SUM(ISNULL ?


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    Using an IIF the syntax would be:  SUM(IIF(ISNULL(theexpression),0,theexpression))  -- assuming that IIF is supported by dbase


    Chuck

    Thursday, April 12, 2012 1:33 PM
  • File 'coalesce.prg' does not exist.  I'll be searching, unless someone on here knows where to acquire or reference that in Visual Studio 2010 C#.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 2:27 PM
  • File 'coalesce.prg' does not exist.  I'll be searching, unless someone on here knows where to acquire or reference that in Visual Studio 2010 C#.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    That message is most likely coming from Dbase through the driver- not VS.

    Chuck

    Thursday, April 12, 2012 2:30 PM
  • Try NVL instead of Coalesce

    http://fox.wikis.com/wc.dll?Wiki~VFPSQL-TSQL-Mapping

    SELECT custlist.supplier, 
    SUM(NVL(cust01.ytdpur, 0)) AS store01, 
    SUM(NVL(cust02.ytdpur, 0)) AS store02, 
    SUM(NVL(cust03.ytdpur, 0)) AS store03, 
    SUM(NVL(cust04.ytdpur, 0)) AS store04, 
    SUM(NVL(cust05.ytdpur, 0)) AS store05 
    FROM custlist 
    LEFT JOIN cust01 ON custlist.custID = cust01.custID  
    LEFT JOIN cust02 ON custlist.custID = cust02.custID 
    LEFT JOIN cust03 ON custlist.custID = cust03.custID 
    LEFT JOIN cust04 ON custlist.custID = cust04.custID 
    LEFT JOIN cust05 ON custlist.custID = cust05.custID 
    
    GROUP BY custlist.custID


    Chuck


    Thursday, April 12, 2012 2:35 PM
  • Is that a downloadable file?  I'm not getting anything conclusive.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 2:37 PM
  • Is that a downloadable file?  I'm not getting anything conclusive.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!


    No - what it is saying is that it does not understand coalesce and is assuming it is an external program.   Try NVL - that seems to be the foxpro version of coalesce

    Chuck

    • Marked as answer by psifreak Thursday, April 12, 2012 2:53 PM
    Thursday, April 12, 2012 2:38 PM
  • That was it!     Greatly appreciated!   NVL, http://technet.microsoft.com/en-us/query/x9xfsceh for those who seek some info on it.  It's amazing how much you can't look up without knowing what it's called.....

    Thanks guys!  And old jeep, that first post with NVL apparently showed up while I was writing the last resposne, no pun intended.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 2:55 PM
  • If you're using Visual FoxPro, then use NVL function, e.g.

    SUM(NVL(CustPay,0)) although NULL is ignored by the engine, so I don't see why do you need to worry about NULL at all.


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


    My blog

    Thursday, April 12, 2012 4:24 PM
  • I suggest to ask questions in Visual FoxPro forum here if you deal with FoxPro or dbase files. In FoxPro the ISNULL function actually just tests if the value is NULL and NVL function converts NULL to the value in the second argument. 

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


    My blog

    Thursday, April 12, 2012 4:27 PM
  • Worried about the null because it caused/causes  errors when crunching those 6 columns into 1 column like that.  When it encountered a null in one of the files, it moved to the next without checking the rest.  This NVL one works to replace the nulls with 0's, if nothing else I can do an object-oriented total on that.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 5:05 PM
  • Can you post your whole code so we can see where the error is? In the aggregate functions like SUM the NULL is not counted.

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


    My blog

    Thursday, April 12, 2012 5:22 PM
  • I've solved the problem, when the NVL command made it replace all the nulls with 0's, I just set up an iterator to go through and add the totals into a new column.  No more errors.

    Thanks a million for the code chunks, those were miracle workers!


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, April 12, 2012 7:25 PM