SQL: Analytical Function SUM with Distinct-CodePudding

Here We have a duplicate ID, Need to get distinct of ID and SUM, So it should be 13 as Amount

I know it is possible to do this by first getting the distinct query and then on top of the query getting the SUM.

Is there a way to achieve this in one single query

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select   * , sum(amount) over (partition by name ,status) from data

CodePudding user response：

There might be a better way to do, but here's my take:

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select sum(Amount) from (select  distinct *  from data) a

CodePudding user response：

Here is my take,

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select sum(Amount) from data d where not exists(select 'x' from data d2 where d2.amount = d.amount and d2.id > d.id)

CodePudding user response：

I'd leverage Snowflake's QUALIFY function to do something along these lines:

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         ),
filter as (
    select *
    from data
    qualify row_number() over (partition by id order by name) = 1
)
select   * , sum(amount) over (partition by name ,status) 
from filter;