Problem getting sum of distinct entries

[morfasie]

I have a table with policy values. But some of them are in the table more than once. I have to sum the Premium amount don't know how to calc the sum for distinct values?

Can anybody please help

Thanks
martin

 

[PHV]

Something like this ?
SELECT policy, SUM(PremiumAmount) AS PremAmt
FROM theTable
GROUP BY policy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[morfasie]

Hi thanks,
that is fine if you have one entry per policy, but I have multiple and only want to do a sum of premium on the distinct ones.

 

[nagornyi]

SELECT 
  policy
, SUM(PremiumAmount) PremAmt
FROM 
  (select distinct policy, PremiumAmount from theTable)
GROUP BY policy

 

[dnoeth]

ANSI SQL supports:

SELECT
policy
, SUM(DISTINCT PremiumAmount)
FROM
my_table
GROUP BY policy

Dieter