How to select Sum data with SQL in Excel for distinct values?-CodePudding

With this SQL code I want to take sum of net amounts by every item number separately:

myquery = "SELECT  distinct " & ItemNo & ", -SUM( " & NetAmount & " )   FROM " & table_TSE & _
    " WHERE " & ItemNo & " IN " & Itemnr1 & _
" AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"

But I got error:

How can I solve it?

CodePudding user response：

You need to have a "GROUP BY" and "HAVING" clause at the end instead of "WHERE" when using aggregate functions like "SUM".

Within the "GROUP BY" clause you state the columns in which are not inside of a "SUM" function.

Then think of "HAVING" as a "WHERE" clause.

I haven't tested but this should work:

myquery = "SELECT  DISTINCT" & ItemNo & ", -SUM( " & NetAmount & " )  FROM " & table_TSE & _
    " GROUP BY [Date]," & ItemNo & " HAVING " & ItemNo & " IN " & Itemnr1 & _
" AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"

Let me know if this helps!

Here is a good source: https://www.w3schools.com/sql/sql_having.asp

CodePudding user response：

SUM() is an aggregate function in SQL, and when used in a select statement with other non-aggregate columns, should be accompanied with a GROUP BY statement containing any non-aggregate columns.

Consider changing your query to include

" GROUP BY " & ItemNo &

Reference Example: https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions#sum_function