1

I have the following output table:

Quote | art0001 | art0002 | art0003 |
=====================================
100   |    4    |    1    |    5    |
99    |    0    |   10    |    1    |
98    |   12    |    5    |   19    |
97    |    1    |    0    |    6    |
96    |    0    |    1    |    0    |
...   |   ...   |   ...   |   ...   |

Well, I need the sum of the columns of art000x to expand the output table with the total amount of the individual articles:

=====================================
      |   17    |   17    |   31    |

Currently the alasql-Code is defined as below:

var sqlRequest = [
        'SELECT Quote, \ ' +
        'art0001, \ ' +
        'art0002, \ ' +
        'art0003, \ ' +
        'art0004, \ ' +
        'art0005, \ ' +
        'art0006, \ ' +
        'art0007, \ ' +
        'art0008, \ ' +
        'art0009, \ ' +
        'art0010, \ ' +
        'art0011, \ ' +
        'art0012, \ ' +
        'art0013, \ ' +
        'art0014 \ ' +
        'INTO XLSXML("Test.xls", ?) FROM ?'
    ];

var opts = {
   headers: true
}

$scope.btnExport = function () {
   alasql(sqlString[0], [opts, $scope.listOfItems]);
}

An additional question: How can I reduce the sql SELECT string?

Thanks for your help!

yuro
  • 2,189
  • 6
  • 40
  • 76

1 Answers1

1

  • No reason to add your \

  • When getting the SUM of each art0xx select SUM(art0xx).

  • When summing up it does not make sense to also get the Quote field -

So I suggest you use:

'SELECT 'SUM(art0001) as art0001, ' +
    'SUM(art0002) as art0002, ' +
    'SUM(art0003) as art0003, ' +
    'SUM(art0004) as art0004, ' +
    'SUM(art0005) as art0005, ' +
    'SUM(art0006) as art0006, ' +
    'SUM(art0007) as art0007, ' +
    'SUM(art0008) as art0008, ' +
    'SUM(art0009) as art0009, ' +
    'SUM(art0010) as art0010, ' +
    'SUM(art0011) as art0011, ' +
    'SUM(art0012) as art0012, ' +
    'SUM(art0013) as art0013, ' +
    'SUM(art0014) as art0014, ' +
    'INTO XLSXML("Test.xls", ?) FROM ?'
mathiasrw
  • 610
  • 4
  • 10
  • You don't understand my problem. My code exports the json data in an excel file. In this excel are the columns of article numbers and their indication of quantities. After these output data I need to add the total quantity of each column. – yuro Jun 01 '16 at 17:40
  • You cant add data to an existing Excel file - only rewrite all of it. The above code will give you the output you requested. – mathiasrw Jun 01 '16 at 20:18
  • How can I add data to an existing Excel file? When I use your proposal then the program create a file `Test(1).xls` – yuro Jun 04 '16 at 00:04
  • You cant add data to existing excel file with AlaSQL. Have a look at https://github.com/SheetJS/js-xlsx – mathiasrw Jun 09 '16 at 11:27