Source language: Translate to:

Sql group by

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sql group by

Postby BRobinsonS » Sat Jan 07, 2017 3:24 pm

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?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql group by

Postby Gaev » Sat Jan 07, 2017 4:39 pm

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.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql group by

Postby BRobinsonS » Sun Jan 08, 2017 8:40 am

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql group by

Postby BRobinsonS » Sun Jan 08, 2017 2:39 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql group by

Postby Gaev » Sun Jan 08, 2017 5:28 pm

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).
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql group by

Postby BRobinsonS » Fri Jan 13, 2017 2:14 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest