select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0;
> On Oct 18, 2017, at 7:23 PM, jose isaias cabrera <jic...@barrioinvi.net> > wrote: > > > CREATE TABLE Tasks ( > id INTEGER PRIMARY KEY, > Pid INTEGER, > bd TEXT, > ed TEXT, > task TEXT, > target TEXT, > amt REAL > ); > > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','it-IT',80); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','it-IT',80); > > I know I can do, > > select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING > amt > 0; > > and get, > > 2017-10-01|es-ES|100.0 > 2017-10-01|fr-FR|185.0 > 2017-10-01|it-IT|200.0 > > but, I would like to add the ed of the task='QUOTE' to the beginning of the > list. So, the result would look like this, > > 2017-09-27|2017-10-01|es-ES|100.0 > 2017-09-27|2017-10-01|fr-FR|185.0 > 2017-09-27|2017-10-01|it-IT|200.0 > > I know how to select it by itself, > > SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; > > but I need to add it to the beginning of the list with a JOIN or something. > Any thoughts? Thanks. > > josé > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
