CODEWITHSUNDEEP
sqlselect
sunsets
sunsets

Reputation: 411

select sum values on different conditions in sql

This is the table I got in the middle of approaching what I want.

Equip unit qty
---------------
a     01    2
a     01    3     
a     09   14
b     01    5
b     09   28

I'm trying to find the value 3+14+5+28.

I want to sum all values in different equipment and different unit but if the unit is same, it should be selected the maximum value (in this case, I want to pick 3 instead 2).

I've been struggling to find ways but still have no idea. How would you do solve the problem?

Upvotes: 0

Views: 1672

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

--Create table from below script

create table eqip(equip varchar(3), unit int, qty int);

--Insert Record for query for testing

insert into eqip
values('a'  ,  01 ,   2),
('a',     01 ,   3    ) ,
('a'  ,   09 ,  14),
('b'  ,   01 ,   5),
('b' ,    09 ,  28)

--Run below query for getting result. Where Row_Number getting PARTITION by unit and qty.

select sum(qty) as total_quantity from(
    select Row_Number() OVER (PARTITION BY equip,unit ORDER BY qty DESC) AS row_num, equip, unit, qty from eqip
) as A where row_num=1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use a subquery:

select sum(qty)
from (select equip, unit, max(qty) as qty
      from t
      group by equip, unit
     ) eu;

Here is an example in Rextester.

You can also do this without a subquery in the from, assuming the maximum is not duplicated:

select sum(qty)
from t
where qty = (select max(t2.qty) from t t2 where t2.equip = t.equip and t2.unit = t.unit);

Upvotes: 1

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

;WITH CTE
AS (
    SELECT Equipment
        ,Unit
        ,Quantity
        ,Row_Number() OVER (
            PARTITION BY Equipment
            ,Unit ORDER BY Quantity DESC
            ) AS RowNum
    FROM SampleTable
    )
SELECT Sum(Quantity) AS Total
FROM CTE
WHERE RowNum = 1;

Upvotes: 0

Related Questions