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
