Function sum on SQL for database

Question

0.00/5 (No votes)

See more:

I want to sum values of a table of my database, and I wrote:

strQuery = _T( "SELECT SUM (pezzi) FROM PRODOTTI " );

but it says that in my database there isn't database.sum...I don't undestand, because it must recogniza functiobn sum

What I have tried:

I tried to search in internet, but they use function sum without problems

Posted 11-Oct-19 4:15am

Member 14594285

Updated 11-Oct-19 4:51am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-10-11T04:52:00

Solution 2

Check your connection string and make sure you are connecting to a specific DB, rather than just connecting to the server.

If you're sure that's right, then use the debugger to check exactly what you are doing with that query string and exactly what you are passing to SQL. (We don't have access to the rest of your code, so we can't check that the string does not get modified from that point on).

SELECT SUM(... works fine on all my systems here, so I'd guess it's something you are doing to the the string later.

Posted 11-Oct-19 4:52am

OriginalGriff

Comments

Member 14594285 11-Oct-19 11:35am

now query works, but a question, when I write code to write my results in a list, I write:
recordset.GetFieldValue(_T("data_scanner"), dtScanner);
recordset.GetFieldValue(_T("somma"), lPezzi);
pLst->SetItemText(nProdotti, 1, strDataScanner);

but there isn't group by data..there is only list of dates

OriginalGriff 11-Oct-19 11:40am

Separate problem, separate question - post it as a new one, so it's open to all here.
And include the full code for the relevant fragment so we all know exactly what you are working with.

Member 14594285 11-Oct-19 11:47am

ok

phil.o · Answer 2 · 2019-10-11T04:23:00

Solution 1

You need to use a GROUP BY clause when you use any aggregation function:

x

strQuery = _T( "SELECT SUM (pezzi) FROM PRODOTTI GROUP BY id " );

Posted 11-Oct-19 4:23am

phil.o

v3

Comments

Member 14594285 11-Oct-19 10:29am

I wrote:
strQuery = _T( "SELECT SUM (pel_pezzi) FROM PRODOTTI GROUP BY PRODOTTI.data_scanner");
if (CFG->m_bDataScanner)
{
strQuery.Append(bWhere ? _T("AND ") : _T("WHERE "));
strQuery.AppendFormat(_T("PRODOTTI.pel_data_scanner BETWEEN '%04ld-%02ld-%02ld %02ld:%02ld:%02ld' AND '%04ld-%02ld-%02ld %02ld:%02ld:%02ld'"),
CFG->m_dtScannerIni.GetYear(), CFG->m_dtScannerIni.GetMonth(), CFG->m_dtScannerIni.GetDay(), CFG->m_dtScannerIni.GetHour(), CFG->m_dtScannerIni.GetMinute(), CFG->m_dtScannerIni.GetSecond(),
CFG->m_dtScannerFin.GetYear(), CFG->m_dtScannerFin.GetMonth(), CFG->m_dtScannerFin.GetDay(), CFG->m_dtScannerFin.GetHour(), CFG->m_dtScannerFin.GetMinute(), CFG->m_dtScannerFin.GetSecond());
bWhere = TRUE;
}

Member 14594285 11-Oct-19 10:30am

but it doesn't work, there is an error in my sintax

phil.o 11-Oct-19 10:33am

Well, it could be interesting to see the final query which is produced. The only thing I can say right now is that there seems to be missing a whitespace after data_scanner. This is a common mistake when building sql queries by concatenation.

Member 14594285 11-Oct-19 10:38am

I wrote only this to try:
strQuery = _T( "SELECT SUM (pel_pezzi), data_scanner FROM PRODOTTI GROUP BY PRODOTTI.data_scanner ");
but it doesn't wotk, it says that in my database there the column SUM, I don't understand

Member 14594285 11-Oct-19 10:46am

now I removed space after SUM and there is an other error, "impossibile trovare l'oggetto desiderato"

Richard Deeming 11-Oct-19 13:31pm

No you don't. If you don't specify a GROUP BY clause, the aggregate applies to the entire table.

The only time you have to use GROUP BY is if you have columns which don't have an aggregate specified.