mysql - SQL query to compare row value to group values, with condition -

i wish port r code hadoop used impala or hive sql-like query. code have based on question:

r data table: compare row value group values, condition

i wish find, each row, number of rows same id in subgroup 1 cheaper price.

let's have following data:

create table project (     id int,     price int,      subgroup int );  insert project(id,price,subgroup)  values     (1, 10, 1),      (1, 10, 1),      (1, 12, 1),     (1, 15, 1),     (1,  8, 2),     (1, 11, 2),     (2,  9, 1),     (2, 12, 1),     (2, 14, 2),     (2, 18, 2); 

here output have (with new column cheaper):

id  price   subgroup   cheaper 1   10      1          0 ( because no row cheaper in id 1 subgroup 1) 1   10      1          0 ( because no row cheaper in id 1 subgroup 1) 1   12      1          2 ( rows 1 , 2 cheaper) 1   15      1          3 1    8      2          0 (nobody cheaper in id 1 , subgroup 1) 1   11      2          2 2    9      1          0 2   12      1          1 2   14      2          2 2   18      2          2 

note want compare rows ones in subgroup 1, when rows in subgroup 2.

you can join table itself, using left join:

select   p.id,   p.price,   p.subgroup,   count(p2.id)   project p left join project p2   on p.id=p2.id , p2.subgroup=1 , p.price>p2.price group   p.id,   p.price,   p.subgroup order   p.id, p.subgroup 

count(p2.id) count rows join succeed (and succeeds there cheaper prices same id , subgroup 1).

the problem expecting 2 rows:

1   10      1          0 1   10      1          0 

but query return one, because i'm grouping id, price, , subgroup. if have unique id in project table group id. please see fiddle here.

or use inline query:

select   p.id,   p.price,   p.subgroup,   (select count(*)    project p2    p2.id=p.id , p2.subgroup=1 , p2.price<p.price) n   project p