Baskey
Member
Registered: 31st May 06
User status: Offline
|
I have written the following query in terradata SQL assistant that gives me the results I am looking for. My issue is it returns over a million lines when all I need is a sum of value. If I remove the Distinct line it just returns the sum but I know I am picking up duplicates. How do I amend it so it is only returning the sum of value, but it is not including and duplicate reference numbers in the sum ?
Iv changed the database/table names so IT don't get pissey
Select
Sum (Value )
,Distinct (Reference number)
From Databasename.Tablename1 T1
Inner Join Databasename. Tablename2 T2
On T1.Primarykeycolumn = t2. Primarykeycolumn
Where Date between '2011/06/01' and '2011/06/30'
and Active = 'YES'
and Type = 'Type1'
Group by 1;
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Isn't it 'SUM(DISTINCT tablename.fieldname)'?
So it'd be -
code:
SELECT
SUM(DISTINCT tablename.Value), tablename.Reference_Number
FROM
Databasename.Tablename1 T1
INNER JOIN
Databasename.Tablename2 T2 ON T1.Primarykeycolumn = t2.Primarykeycolumn
WHERE
tablename.date BETWEEN ('2011/06/01' AND '2011/06/30')
AND tablename.Active = 'YES'
AND tablename.Type = 'Type1'
GROUP BY
1
Don't really use MSSQL so this could possibly be wrong, although wouldn't you group by tablename.Reference_Number?
Edit - I'd also use an alias on the returned SUM() field.
[Edited on 18-08-2011 by Dom]
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
Yes my bad Dom it should be grouped by reference number. It will need to be distinct on the reference number as there are many values the same.
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
As I don't want it to return the reference numbers I think I need to remove them from the select statement, I'm just not sure where/how to put a condition in to ignore duplicate lines (ref number is the best for this as they should be unique)
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Baskey
It will need to be distinct on the reference number as there are many values the same.
Not sure you need the 'DISTINCT reference name' as the GROUP BY will be doing that anyways.
quote:
Originally posted by Baskey
As I don't want it to return the reference numbers I think I need to remove them from the select statement, I'm just not sure where/how to put a condition in to ignore duplicate lines (ref number is the best for this as they should be unique)
Remove the 'reference number' from the SELECT; as above GROUP BY will do the grouping of the reference numbers.
[Edited on 18-08-2011 by Dom]
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
Just logged off mate. Will try in the morning and let you know how I get on. Cheers
|
