CODEWITHSUNDEEP
sqlsql-server
22332112
22332112

Reputation: 2397

Sum values from one column if Index column is distinct?

How do I sum values from one column when index column is distinct?

Initially, I had this SQL query:

SELECT COALESCE(SUM(ISNULL(cast(Quantity as int),0)),0) AS QuantitySum FROM Records

Also tried to do this, but this is incorrect when some Quantity values happen to be the same:

SELECT COALESCE(SUM(DISTINCT ISNULL(cast(Quantity as int),0)),0) AS QuantitySum FROM Records

How can I fix this query to sum only records quantity that is distinct by Index value?

Example of Table:

Index    Quantity
AN121    40
AN121    40
BN222    120
BN111    20
BN2333   40

So.. I want to return 220

I have duplicate Ids, but quantity can be the same for different records

Upvotes: 2

Views: 903

Answers (3)

kiks73
kiks73

Reputation: 3758

You can use this, if you have duplicated Ids and Quantity:

SELECT COALESCE(SUM(DISTINCT ISNULL(cast(Quantity as int),0)),0) AS QuantitySum 
FROM (SELECT Id, Min(Quantity) From Records group by Id)

Upvotes: 0

MCP_infiltrator
MCP_infiltrator

Reputation: 4179

You can try something like:

SELECT DISTINCT COL1
, SUM(COL2)
FROM MYTABLE
GROUP BY COL1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Do you mean that you only want to sum one value of quantity for each individual value of the index column?

select sum(case when row_number() over (partition by `index` order by newid()) = 1
                then cast(Quantity as int)
           end) as QuantitySum
from Records;

Or, do you mean that you only want to sum values of quantity when there is exactly one row with a given index value:

select sum(case when count(*) over (partition by `index`) = 1
                then cast(Quantity as int)
           end) as QuantitySum
from Records;

Both of these use window functions to restrict the values being processed.

Also, a column called quantity should be stored as a numeric type, so conversion isn't needed to take the sum.

Upvotes: 2

Related Questions