Re: [sqlite] Grouping and grabbing one item

jose isaias cabrera Wed, 18 Oct 2017 15:41:25 -0700


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 ideahow 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 Taskscl 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

Re: [sqlite] Grouping and grabbing one item

Reply via email to