How about 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;
2017-09-27|2017-10-01|es-ES|170.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 18 October 2017 at 18:23, 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
