CODEWITHSUNDEEP
sqlsql-server
kqlambert
kqlambert

Reputation: 2711

I want only the first value for distinct ids to be summed sql server

I have database like this:

weekid    overage    type    type2
504       400        x       y
504       400        z       y
503       200        x       z
502       100        x       x
502       100        x       y

What I want is to get the 400 from weekid 504 to sum with the 200 of weekid 503 and the 100 from weekid 502, I do not want it to sum the other 400 from weekid 504 or 502

So the output should be sum(overage) = 700

basically what i want is something that does a vbscript loop like this

do until rs9.eof
    if weekid<>rs.fields("weekid") then
        weekid=rs.fields("weekid")
        overage=overage+rs.fields("overage")
    end if
rs.movenext
loop

however it needs to do this in the query, what is the easiest sql query to do this?

also my version of sql server does not seem to support row_number

Upvotes: 0

Views: 152

Answers (3)

Kash
Kash

Reputation: 9029

Try this;

If the values for same weekids would ALWAYS be the same, then use a).
For the case when the same weekids have different overage values:

a) if you need the different values for the same value to be included in the sum: 

SELECT  SUM([Overage])
FROM    ( SELECT DISTINCT
                    [WeekId] ,
                    [Overage]
          FROM      [dbo].[OverageTable]
        ) tmp

a) if you need only the max of different values for the same value to be included in the sum: 

SELECT  SUM([Overage])
FROM    ( SELECT    [WeekId] ,
                    MAX([Overage]) AS [Overage]
          FROM      [dbo].[OverageTable]
          GROUP BY  [WeekId]
        ) tmp

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can randomly choose one value:

select sum(overage)
from (select t.*,
             row_number() over (partition by weekid order by newwid()) as seqnum
      from t
     ) t
where seqnum = 1

If you know all the values are the same, you might prefer an aggregation instead:

select sum(overage)
from (select weekid, max(overage) as overage
      from t
      group by weekid
     ) t

DON'T DO THE FOLLOWING:

select sum(distinct overage)

Although this is valid SQL, you will lose weeks that have the same value.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

That depends what you mean by the first row - data has no inherent order. You can tweak the order by clause in the below when you decide how you want to order things,.

select sum(overage)
from
    (Select overage, row_number() over (partition by weekid order by type, type2) rn
     from yourtable
    ) v
where rn=1

Upvotes: 0

Related Questions