Since you are eventually going to come to the part where you need to include
the quote for the correct target, lets include that too, just to skip ahead:
select (SELECT ed
from Tasks as I
where I.task = 'QUOTE'
and I.Pid = O.Pid
and I.target like '%' || O.Target || '%'
),
max(O.ed),
O.target,
sum(O.amt)
from Tasks as O
where ...
group by target
having sum(amt) > 0;
NB: The data is not relational. You should make it so and not overload
multiple entries into a single value (ie, make sure your data is at in at least
first normal form). Then you can replace the "like" with a simple equals.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of jose isaias cabrera
>Sent: Wednesday, 18 October, 2017 16:39
>To: SQLite mailing list
>Subject: Re: [sqlite] Grouping and grabbing one item
>
>
>Ok, I missed a condition. Imagine this set of data,
>
>
>CREATE TABLE Tasks (
> id INTEGER PRIMARY KEY,
> Pid INTEGER,
> cust TEXT,
> period TEXT,
> bd TEXT,
> ed TEXT,
> task TEXT,
> target TEXT,
> amt REAL
>);
>
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',100);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',120);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',110);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',70);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',75);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(1,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',80);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','DOC','es-ES',100);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','DOC','it-IT',120);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','DOC','fr-FR',110);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','VAL','es-ES',70);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','VAL','fr-FR',75);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(2,'Z','aa','2017-09-28','2017-10-01','VAL','it-IT',80);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-10-03','2017-10-04','QUOTE','es-ES fr-FR it-IT',0);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',200);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',320);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',410);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',170);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',275);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(3,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',180);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','DOC','es-ES',100);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','DOC','it-IT',120);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','DOC','fr-FR',110);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','VAL','es-ES',70);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','VAL','fr-FR',75);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(4,'F','aa','2017-09-28','2017-10-01','VAL','it-IT',80);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','DOC','es-ES',100);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','DOC','it-IT',120);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','DOC','fr-FR',110);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','VAL','es-ES',70);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','VAL','fr-FR',75);
>INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES
>(5,'F','ab','2017-09-28','2017-10-01','VAL','it-IT',80);
>
>I know I can do,
>
>select max(ed),target, sum(amt) from Tasks where cust='A' group by
>Pid,
>target HAVING amt > 0;
>
>to get,
>
>2017-10-01|es-ES|170.0
>2017-10-01|fr-FR|185.0
>2017-10-01|it-IT|200.0
>2017-10-01|es-ES|370.0
>2017-10-01|fr-FR|685.0
>2017-10-01|it-IT|500.0
>
>but I want the QUOTE ed for the correct Pid in front again. I have
>no idea
>how to do this. I was trying some JOINs,
>
>
>select ls.ed,max(ls.ed),ls.target, sum(amt) from Tasks ls JOIN Tasks
>cl ON
>(
>ls.Pid = cl.Pid AND
> ls.task = 'QUOTE'
>) where cust='A' group by Pid, target HAVING amt > 0;
>
>sqlite> select ls.ed,max(ls.ed),ls.target, sum(amt) from Tasks ls
>JOIN Tasks
>cl ON
> ...> (
> ...> ls.Pid = cl.Pid AND
> ...> ls.task = 'QUOTE'
> ...> ) where cust='A' group by Pid, target HAVING amt > 0;
>Error: ambiguous column name: amt
>
>I need to get,
>
>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
>2017-10-04,2017-10-01|es-ES|370.0
>2017-10-04,2017-10-01|fr-FR|685.0
>2017-10-04,2017-10-01|it-IT|500.0
>
>Any help would be great. I have to read on some JOINs. Thanks.
>
>
>-----Original Message-----
>From: no...@null.net
>Sent: Wednesday, October 18, 2017 3:17 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Grouping and grabbing one item
>
>On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote:
>
>> 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;
>
>When I first read that query I wondered if putting a query inside a
>select expression would execute it for each row. I don't know if that
>is the case, but here is the query plan:
>
> explain query plan 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;
>
>0,0,0,"SCAN TABLE Tasks"
>0,0,0,"USE TEMP B-TREE FOR GROUP BY"
>0,0,0,"EXECUTE SCALAR SUBQUERY 1"
>1,0,0,"SCAN TABLE Tasks"
>
>It would appear that moving the subquery down into a FROM clause
>makes the
>query plan look slightly better. In my humble opinion it also makes
>the
>query easier to understand.
>
>explain query plan
>select
>q.ed,
>max(tasks.ed),
>target,
>sum(amt)
>from
>(SELECT ed from Tasks where task = 'QUOTE' and Pid = 1) q
>inner join
>Tasks
>on
>Pid=1
>group by
>q.ed,
>target
>HAVING
>amt > 0;
>
>0,0,0,"SCAN TABLE Tasks"
>0,1,1,"SEARCH TABLE Tasks USING AUTOMATIC PARTIAL COVERING INDEX
>(Pid=?)"
>0,0,0,"USE TEMP B-TREE FOR GROUP BY"
>
>The above is the case with version 3.16.2.
>
>
>--
>Mark Lawrence
>_______________________________________________
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
