Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.
This article explains how to obtain aggregate results using the following SQL keywords:
You use these SQL functions to obtain statistical information about the records in a data source rather than to extract records from the data source. The recordset that is created usually consists of a single record (if all columns are aggregates) that contains a value. (There might be more than one record if you used a GROUP BY clause.) This value is the result of the calculation or extraction performed by the SQL function.
Tip To add an SQL GROUP BY clause (and possibly a HAVING clause) to your SQL statement, append it to the end of m_strFilter. For example:
m_strFilter = "sales > 10 GROUP BY SALESPERSON_ID";
You can limit the number of records you use to obtain aggregate results by filtering and sorting the columns.
Caution Some aggregation operators return a different data type from the column(s) over which they are aggregating.
For example, ClassWizard creates long
m_lSales
to accommodate a Sales column, but you’ll need to replace this with a double m_dblSumSales
data member to accommodate the aggregate result. See the example that follows.
To obtain an aggregate result for a recordset
RFX_Long(pFX, "Sales", m_lSales);
with
RFX_Double(pFX, "Sum(Sales)", m_dblSumSales)
m_dblSumSales
.Note ClassWizard actually assigns data member names without Hungarian prefixes. For example, the wizard would produce m_Sales
for a Sales column, rather than the m_lSales
name used earlier for illustration.
If you’re using a CRecordView class to view the data, you’ll have to change the DDX function call to display the new data member value; in this case, changing it from
DDX_FieldText(pDX, IDC_SUMSALES, m_pSet->m_lSales, m_pSet);
to
DDX_FieldText(pDX, IDC_SUMSALES, m_pSet->m_dblSumSales, m_pSet);