CODEWITHSUNDEEP
phpsql
jhunlio
jhunlio

Reputation: 2660

sum() by group sql

I have data in a row with same value and id example below:

table_name: test

id | amount
 1 |   100
 1 |   100
 1 |   100 
 2 |   150
 2 |   150
 2 |   150
 3 |   200
 3 |   200
 3 |   200
 4 |   250
 4 |   250
 4 |   250

I want to sum only one row in each idusing sql below is not working it sum all the row.

"select *, sum(amount) as total from test group by id";

my question is that possible to sum() only one row each id?

desired output?(edit)

id | amount
 1 |   100
 2 |   150
 3 |   200
 4 |   250

total : 700

Upvotes: 0

Views: 89

Answers (5)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Try this

create table #test
(id int, amount int)

insert into #test values (1,100),(1,100),(1,100),(2,150),(2,150),(3,200),(3,250)

;with cte
as
(
select sum(distinct amount) as damount,id
from #test
group by id
)

select sum(damount) as total from cte

For other DBMSs than SQL Server

select sum(damount) as total from (select sum(distinct amount) as damount,id
from test
group by id) as it

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

my question is that possible to sum() only one row each id?

I interpret this as your wanting one value, with a single row from each group. One method is two levels of aggregation:

select sum(amount)
from (select id, max(amount) as amount
      from test
      group by id
     ) t;

Upvotes: 1

Urvesh
Urvesh

Reputation: 11

try this using subquery-

select sum(amount) from (select distinct id,amount from company7) as tempTable

Upvotes: 1

Justin
Justin

Reputation: 9724

Try:

select id, sum(amount)/count(*) as total 
from test 
group by id

Result:

| id | total |
|----|-------|
|  1 |   100 |
|  2 |   150 |
|  3 |   200 |
|  4 |   250 |

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Looks like you need this

select *,sum(amount) from (select distinct * from test) as t group by id

Upvotes: 1

Related Questions