Grouping and grabbing one item

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Grouping and grabbing one item

jose isaias cabrera-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

Darko Volaric
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

jose isaias cabrera-3

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 <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

Paul Sanderson
In reply to this post by jose isaias cabrera-3
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

Paul Sanderson
In reply to this post by jose isaias cabrera-3
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 <[hidden email]>
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 <[hidden email]>
>> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

jose isaias cabrera-3
In reply to this post by Paul Sanderson

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 <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

nomad
In reply to this post by Darko Volaric
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

jose isaias cabrera-3

This last one does appear faster...  Thanks.



-----Original Message-----
From: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

jose isaias cabrera-3
In reply to this post by nomad

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: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

Keith Medcalf

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-
>[hidden email]] 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: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and grabbing one item

Clemens Ladisch
In reply to this post by nomad
[hidden email] wrote:
> 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.

Only correlated subqueries are executed for each row.  All other
subqueries are executed only once (when they are first needed).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users