MATLAB: Does the SQL Query with a SUM function and a second field not work with the Database Toolbox 3.0.2 (R14SP2)

accessdatabaseDatabase Toolboxdriverfieldsmicrosoftmultiplequerysum

When I execute the following code against a database
conn = database('WavePlan', '', '');
curs=exec(conn, 'SELECT ALL POOL_PT,SUM(CASES) FROM ALLOCATION ');
curs
curs.message
I receive the following result
 curs = 
               Attributes: []
                       Data: 0
     DatabaseObject: [1x1 database]
                RowLimit: 0
              SQLQuery: 'SELECT ALL POOL_PT,SUM(CASES) FROM ALLOCATION '
                Message: [1x161 char]
                       Type: 'Database Cursor Object'
               ResultSet: 0
                   Cursor: 0
               Statement: 0
                     Fetch: 0
 curs.message=
 [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'POOL_PT' as part of an aggregate function.

Best Answer

This is not a bug in MATLAB; rather, the query is considered illegal by the Microsoft Access ODBC driver which returns the error to MATLAB. The value of 'curs.message' from the Microsoft Access ODBC driver indicates this. Other databases may support this query but the Microsoft Access ODBC driver does not.
To workaround this issue, consider using the MATLAB functionality to calculate the total sum of a column in a given selection. The following example demonstrates how this can be done:
conn = database('WavePlan', '', '');
curs=exec(conn, 'SELECT ALL POOL_PT,CASES FROM ALLOCATION ');
curs=fetch(curs);
b=curs.data;
a=sum(cell2mat(curs.data(:,2)));
b(:,2)=num2cell(a);
The variable 'b' contains the output which would have been returned if the SQLQuery: 'SELECT ALL POOL_PT,SUM(CASES) FROM ALLOCATION ' were supported by the Microsoft Access ODBC driver.
Related Question