Re: [sqlite] Grouping and grabbing one item

[jose isaias cabrera]

Thanks, I didn't know that was possible.
-----Original Message----- 
From: Paul Sanderson
Sent: Wednesday, October 18, 2017 2:10 PM
To: SQLite mailing list
Subject: Re: [sqlite] Grouping and grabbing one item

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