sql - SQL - get min values from a column based on values from two other columns

Question

I am trying to collect values of a column that are less than another value of the column that has a specific value in a second column for each instance of a unique value in a third column.

Here's an example.

user_name	time	succeeds
bill	0300	no
bill	0400	yes
bill	0500	no
annie	1200	yes
annie	1400	yes
jonny	0900	no
jonny	1000	no
jonny	1400	yes
jonny	1900	yes

So for each user, I want to find the earliest time they succeed (a 'yes') and then collect all the times below these times.

For bill, this would be 0300 For annie there is no time For jonny this would be 1000 and 0900

标签： sqlsqlite

Answer 1

select t1.*,t2.sumTimeUnSucceeds,t2.concatenateTimeUnSucceeds from
(select user_name,min(time1) as minTimeSucceeds from Mytable 
 where succeeds = "yes" group by user_name) as t1
left join 
(select user_name,sum(time1) as sumTimeUnSucceeds, 
 group_concat(time1) as concatenateTimeUnSucceeds from Mytable 
 where succeeds = "no" group by user_name) as t2
on t1.user_name = t2.user_name;

SQL小提琴