"Igor Tandetnik" wrote...
On 5/30/2014 1:29 PM, jose isaias cabrera wrote:
"Igor Tandetnik" wrote...
On 5/30/2014 12:41 PM, jose isaias cabrera wrote:
What should be returned is
the value of vEmail of the first record that has Xtra4='y'
What do you mean by "first record"? Records are processed in no
particular order.
Good point. I was, wrongly, thinking that it was top to bottom with the
id. So, the idea is that once Xtra4='y' has provided a value, that is
what I want.
When you say "the id", what exactly do you mean? If you mean ProjID,
then you are grouping by it, so all rows in a group would have the same
value, and it's again unclear which one is "first". Is there some other
"id" that doesn't currently appear in the statement?
yes, it's id. So, here is a sample..
create table t
(
id integer primary key,
ProjID integer,
invoice,
ProjFund,
vEmail,
Xtra4
);
insert into t values(1,1,70,100,'a','n');
insert into t values(2,1,30,50,'b','n');
insert into t values(3,1,25,40,'c','y');
insert into t values(4,1,55,80,'d','y');
insert into t values(5,2,30,75,'e','n');
insert into t values(6,2,10,100,'f','y');
insert into t values(7,2,40,68,'g','y');
insert into t values(8,2,30,55,'h','n');
insert into t values(9,3,25,40,'i','y');
insert into t values(10,3,30,100,'j','n');
select ProjID,
case Xtra4 when 'y' then vEmail else 'noemail' end,
sum(ProjFund),
sum(ProjFund) - sum(case Xtra4 when 'y' then invoice else 0 end),
sum(case Xtra4 when 'y' then invoice else 0 end)
FROM t GROUP BY ProjID
HAVING sum(case Xtra4 when 'y' then invoice else 0 end) > 0;
which gives me this result,
1|d|270|190|80
2|noemail|298|248|50
3|noemail|140|115|25
But I want,
1|d|270|190|80
2|f|298|248|50
3|i|140|115|25
or
1|d|270|190|80
2|g|298|248|50
3|i|140|115|25
Maybe that helped.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
