ahh bugger - google didn't show the new answers had popped up. Pleased I came up with a working solution though :)
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 19:03, jose isaias cabrera <jic...@barrioinvi.net> wrote: > > So simple! Thanks, Darko. > > -----Original Message----- From: Darko Volaric > Sent: Wednesday, October 18, 2017 1:57 PM > To: SQLite mailing list > Subject: Re: [sqlite] Grouping and grabbing one item > > > 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
