CODEWITHSUNDEEP
mysqlsqloracle-database
Jain
Jain

Reputation: 999

Query Does not incluse expression as a part of aggregate function

below table is a shorter version of my table. I want to aggregate two columns which have common Id. The role of security does not matter, but cannot remove it from my select statement.

INPUT

Security           MValue        BValue          ID 
 ABH23              50             55             1  
 ABH52              20             20             2
 ABH96              30             35             3
 LOH27              80             70             3
 LOH52              10             15             2
 KLO12              70             60             1

OUTPUT

 Security             MValue        BValue          ID 
 Doesn't Matter       120             115            1  
 Doesn't Matter       30              35             2
 Doesn't Matter       110             105            3

SELECT Security,sum(MValue),sum(Bvalue),ID Group BY ID

Now I understand that after aggregating there will be multiple security value for one record but I don't care as which one gets selected in my final output, but I am sure there is a way to solve this.

Upvotes: 0

Views: 51

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Try this.

select Security, MValue, BValue, ID  from 
(
SELECT Row_number()over(partition by ID order by ID) as Rn, Security,sum(MValue) over(partition by ID) as MValue,sum(Bvalue) over(partition by ID) as BValue,ID
from  Yourtable
)
Where RN=1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Just select one use min() or max():

SELECT MIN(Security), sum(MValue), sum(Bvalue), ID
FROM ???
Group BY ID;

Upvotes: 2

Related Questions