Sql group by

[BRobinsonS]

The following Sql statement works.

Code: Select all

dbpExecSQL "NBAccounting" "INSERT INTO IncomeExpenseTbl (AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]# AND AccountNo >= [#34]4000[#34]) GROUP BY AccountNo,AccountName,AccountType " ""

However, I only want to 'Group By' AccountNo, but I get an error.

What do I not understand?

[Gaev]

Brian:

The following Sql statement works.

Are you sure ? ... the WHERE clause does not appear to have the proper syntax ... try ...

WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#) AND (AccountNo >= [#34]4000[#34])

... and if AccountNo is not a text/string field ...

WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#) AND (AccountNo >= 4000)

I only want to 'Group By' AccountNo, but I get an error.

Post the exact command ... and the error message/text.

Might be a good idea to place a semicolon at the end of the SQL command.

If all else fails, try one (itty bitty) step at a time e.g. ...

INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails;

... and then ...

INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
GROUP BY AccountNo;

... and then ...

INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#)
GROUP BY AccountNo;

... and finally ...

INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#) AND (AccountNo >= 4000)
GROUP BY AccountNo;


See where it balks.

[BRobinsonS]

Tried to try your first statement

Code: Select all

dbpExecSQL "NBAccounting" "INSERT INTO IncomeExpenseTbl(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails;" ""

Fields exist in both tables i.e. IncomeExpenseTbl, JrDetails.

And got the error

You tried to execute a query that does not include the specified expression 'AccountNo' as part of a aggregate function.

AccountNo is String (10)

This should have not caused an error. I have looked and looked at the statement but can't find the error.

[BRobinsonS]

Found another statement which works in another section in the code
which works as expected by GROUP BY AccountNo.

Code: Select all

dbpExecSQL "NBAccounting" "INSERT INTO SumPreAccount (AccountNo,AcctSumDB,AcctSumCR,SumDiff) SELECT AccountNo,SUM(InAmt) AS AcctSumDB,SUM(OutAmt) AS AcctSumCR,(SUM(InAmt)-Sum(OutAmt)) AS SumDiff FROM JrDetails  WHERE AccountNo = [#34][NBAccounting.Gl.AccountNo][#34] AND JrDetailsDate < #[FromDate]# GROUP BY AccountNo;" ""

Still don't understand and haven't found the problem.

[Gaev]

Brian:

You tried to execute a query that does not include the specified expression 'AccountNo' as part of a aggregate function.

Ooops ... looks like it does not know what field/column to SUM over ... try and start with ...

INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
GROUP BY AccountNo;

... or ... remove the SUM(xxx) fields for now.

Found another statement which works in another section in the code

The difference appears to be in the WHERE clause ... might want to play around first with something simple like ...

WHERE AccountNo >= [#34]4000[#34]

... and if it works, try using the brackets to separate the two conditions (as suggested in my previous email).

[BRobinsonS]

To my surprise. I found out the three fields in

Code: Select all

SELECT AccountNo,AccountName,AccountType

must be represented in the GROUP BY clause in order to work.

I believe I found this on a Microsoft questions website.