What is wrong with this SQL statement?
I have a SQL statement that selects seven columns, six of which come from case statements. It returned duplicate rows, so I made it a select distinct. That eliminated the duplicates. Then I decided I wanted to add an eighth column - sum(total). Now the duplicate rows I thought I had eliminated have returned. The sql is still select distinct, but when I have the sum column, it seems as though the distinct aspect is somehow overriden. What is wrong here?
Its just SQL in a SQL processor. My editor will not allow more than one "Distinct" anywhere in the statement. There are no functions and I cannot use Ifs and Ors. Nulls were a problem, but I’m using the case statements to fill Nulls with acceptable values. I’d like a temp table myself, but I’m not authorized to create one.
On some SQL processors you can’t use SUM and Distinct in the same statement. To get around this run your SQL with the Distinct clause and dump the results into a temporary table, then run the sum function against the temp table.
September 3rd, 2009 at 6:21 am
You need to use the distinct clause in the sum function too
References :
September 3rd, 2009 at 6:29 am
On some SQL processors you can’t use SUM and Distinct in the same statement. To get around this run your SQL with the Distinct clause and dump the results into a temporary table, then run the sum function against the temp table.
References :
September 3rd, 2009 at 7:02 am
could be like a null related problem?
space issue?
are you calling functions?
try using if and or statements to cover the duplicates?
kinda hard for me to answer without actually looking at your database and the actual SQL statement.
References :