CODEWITHSUNDEEP
mysqlsql
Howard
Howard

Reputation: 19805

Aggregate sum of a field when another row with the same ID is selected in SQL

I have two tables

CREATE TABLE users
(
  id int
);

CREATE TABLE attributes
(
  user_id int,
  k char(100),
  v char(100)
);

insert into users values (1);
insert into users values (2);
insert into users values (3);

insert into attributes values (1, 'k1', 'v1');
insert into attributes values (1, 'k2', 2);
insert into attributes values (2, 'k1', 'v1');
insert into attributes values (2, 'k2', 7);
insert into attributes values (3, 'k1', 'v2');
insert into attributes values (3, 'k2', 11);

What I want is

So I want result like

 v1 9
 v2 11

Current SQL:

SELECT v, '?' as total 
FROM users 
JOIN attributes ON users.id = attributes.user_id 
WHERE k = 'k1' 
GROUP BY v

http://sqlfiddle.com/#!2/36920/3/0

Upvotes: 1

Views: 113

Answers (2)

russ
russ

Reputation: 624

create table sumv
(
vnmbr nchar(2),
v int
)
declare @nmbr nvarchar
set @nmbr = 1
while @nmbr < 3
begin
insert into sumv (vnmbr,v)
select 'v'+@nmbr,v from attributes where [user_id] in (select [user_id] from attributes
where v = 'v'+@nmbr) and v not like 'v%'
set @nmbr = @nmbr + 1
end
select vnmbr,sum(v) as [sumv] from sumv group by vnmbr

Upvotes: 0

Bryan
Bryan

Reputation: 6752

Here's exactly what you need. I would completely agree with aardvarkk though, that the wiser choice in the end will likely be to just simplify your schema. Anyways..

SELECT a1.v, SUM(a2.v) FROM users u
INNER JOIN attributes a1 ON a1.user_id = u.id
INNER JOIN attributes a2 ON a2.user_id = a1.user_id AND a2.k = 'k2'
WHERE a1.k = 'k1'
GROUP BY a1.v

Upvotes: 2

Related Questions