SELECT
(
SELECT Sum(tbl_allTransactions.transAmount) AS SumOftransAmount
FROM tbl_allTransactions
WHERE (((tbl_allTransactions.[transType])='Expense')) OR (((tbl_allTransactions.[transType])='Budget') AND ((tbl_allTransactions.[transMonth])=[@transMonth]))
GROUP BY tbl_allTransactions.transMonth
,
SELECT Sum(tbl_allTransactions.transAmount) AS SumOftransAmount
FROM tbl_allTransactions
WHERE (((tbl_allTransactions.[transType])='Expense')) OR (((tbl_allTransactions.[transType])='Budget') AND ((tbl_allTransactions.[transMonth])=[@transMonth]))
GROUP BY tbl_allTransactions.transMonth
)
FROM tbl_allTransactions WHERE (((tbl_allTransactions.[userID])=[@userID]))
I am getting following error:-
System.Data.OleDb.OleDbException (0x80040E14) Syntax error in query
expression '(SELECT Sum(tbl_allTransactions.transAmount) AS
SumOftransAmount FROM tbl_allTransactions WHERE
(((tbl_allTransactions.[transType])='Expense')) OR
(((tbl_allTransactions.[transType])='Budget') AND
((tbl_allTransactions.[transMonth])=[@transMonth])) GROUP '. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResulthr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Obje
The original code was missing a closing parenthesis on the first subquery. The code was also simplified by consolidating the transType conditional from 2 statements to 1. Extra brackets were removed to enhance readability. Also column aliases were added to output of each subquery. Since both queries render the same data, the second column was called "SumOftransAmount2"
SELECT
(SELECT Sum(tbl_allTransactions.transAmount)
FROM tbl_allTransactions
WHERE tbl_allTransactions.transType in ('Expense', 'Budget') AND tbl_allTransactions.transMonth = @transMonth
GROUP BY tbl_allTransactions.transMonth) as SumOftransAmount
,
(SELECT Sum(tbl_allTransactions.transAmount)
FROM tbl_allTransactions
WHERE tbl_allTransactions.transType in ('Expense', 'Budget') AND tbl_allTransactions.transMonth = @transMonth
GROUP BY tbl_allTransactions.transMonth) as SumOftransAmount2
FROM tbl_allTransactions
WHERE tbl_allTransactions.userID=@userID
User contributions licensed under CC BY-SA 3.0