Grouping guidance

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Grouping guidance

Joseph L. Casale
Hi,
I have data that resembles the following:

Request           Number            Description
REQ0090887  TASK0236753  Foo
REQ0090887  TASK0234920  Bar
REQ0090887  TASK0234921  Bar
REQ0090887  TASK0237261  Foo
REQ0086880  TASK0224045  Foo
REQ0086903  TASK0224555  Bar
REQ0086990  TASK0223977  Bar
REQ0087061  TASK0226748  Foo
REQ0087061  TASK0223810  Bar

I want to group request values where each request contains both a
task with description Foo and Bar. So in the above example, only
REQ0090887 and REQ0087061 meet this.

I would be grateful for any guidance on how to write this.

Thanks,
jlc
_______________________________________________
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 guidance

Igor Tandetnik-2
On 12/13/2018 1:58 PM, Joseph L. Casale wrote:

> I have data that resembles the following:
>
> Request           Number            Description
> REQ0090887  TASK0236753  Foo
> REQ0090887  TASK0234920  Bar
> REQ0090887  TASK0234921  Bar
> REQ0090887  TASK0237261  Foo
> REQ0086880  TASK0224045  Foo
> REQ0086903  TASK0224555  Bar
> REQ0086990  TASK0223977  Bar
> REQ0087061  TASK0226748  Foo
> REQ0087061  TASK0223810  Bar
>
> I want to group request values where each request contains both a
> task with description Foo and Bar. So in the above example, only
> REQ0090887 and REQ0087061 meet this.

select Request from MyTable group by Request having count(distinct Description) = 2

--
Igor Tandetnik

_______________________________________________
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 guidance

Joseph L. Casale
-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf
Of Igor Tandetnik
Sent: Thursday, December 13, 2018 12:16 PM
To: [hidden email]
Subject: Re: [sqlite] Grouping guidance

> select Request from MyTable group by Request having count(distinct
> Description) = 2

Nice, I managed it with a join but this is far simpler.

Thanks!
jlc
_______________________________________________
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 guidance

Don V Nielsen
Making a mountain out of a mole hill, but isn't the solution more complex
that that? The description has to be Foo & Bar. But if given the following,
then the simple answer dies.

create table requests (request,task,description);
insert into requests values
  ('REQ0090887','TASK0236753','Foo'),
  ('REQ0090887','TASK0234920','Bar'),
  ('REQ0090887','TASK0234921','Bar'),
  ('REQ0090887','TASK0237261','Foo'),
  ('REQ0090887','TASK0237261','Wrench'),
  ('REQ0086880','TASK0224045','Foo'),
  ('REQ0086880','TASK0224045','Wrench'),
  ('REQ0086903','TASK0224555','Bar'),
  ('REQ0086990','TASK0223977','Bar'),
  ('REQ0087061','TASK0226748','Foo'),
  ('REQ0087061','TASK0223810','Bar');

with exclude_requests as (
  select distinct request from requests where description not in ('Foo','Bar')
)
select request from requests
where request not in (select request from exclude_requests)
group by request
having count(distinct description) = 2
order by request


Doesn't one need to exclude the outliers first?

Have a good one




On Thu, Dec 13, 2018 at 1:44 PM Joseph L. Casale <[hidden email]>
wrote:

> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf
> Of Igor Tandetnik
> Sent: Thursday, December 13, 2018 12:16 PM
> To: [hidden email]
> Subject: Re: [sqlite] Grouping guidance
>
> > select Request from MyTable group by Request having count(distinct
> > Description) = 2
>
> Nice, I managed it with a join but this is far simpler.
>
> Thanks!
> jlc
> _______________________________________________
> 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 guidance

Igor Tandetnik-2
On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> Making a mountain out of a mole hill, but isn't the solution more complex
> that that? The description has to be Foo & Bar. But if given the following,
> then the simple answer dies.

select Request from MyTable group by Request
having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;

--
Igor Tandetnik


_______________________________________________
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 guidance

Igor Tandetnik-2
On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> On 12/13/2018 3:27 PM, Don V Nielsen wrote:
>> Making a mountain out of a mole hill, but isn't the solution more complex
>> that that? The description has to be Foo & Bar. But if given the following,
>> then the simple answer dies.
>
> select Request from MyTable group by Request
> having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;

... and sum(Description not in ['Foo', 'Bar']) = 0

Add conditions to taste.
--
Igor Tandetnik

_______________________________________________
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 guidance

Don V Nielsen
Two thumbs up on Igor. They say the quickest way to the correct answer is
to post the wrong answer on the internet. Hypothesis just proven.

On Thu, Dec 13, 2018 at 3:00 PM Igor Tandetnik <[hidden email]> wrote:

> On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> > On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> >> Making a mountain out of a mole hill, but isn't the solution more
> complex
> >> that that? The description has to be Foo & Bar. But if given the
> following,
> >> then the simple answer dies.
> >
> > select Request from MyTable group by Request
> > having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;
>
> ... and sum(Description not in ['Foo', 'Bar']) = 0
>
> Add conditions to taste.
> --
> Igor Tandetnik
>
> _______________________________________________
> 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 guidance

Roman Fleysher
Hypothesis can never be proven. It can only be rejected with data contradicting it at hand.

"..the quickest way ..." implies someone else corrects you.

________________________________________
From: sqlite-users [[hidden email]] on behalf of Don V Nielsen [[hidden email]]
Sent: Thursday, December 13, 2018 4:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Grouping guidance

Two thumbs up on Igor. They say the quickest way to the correct answer is
to post the wrong answer on the internet. Hypothesis just proven.

On Thu, Dec 13, 2018 at 3:00 PM Igor Tandetnik <[hidden email]> wrote:

> On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> > On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> >> Making a mountain out of a mole hill, but isn't the solution more
> complex
> >> that that? The description has to be Foo & Bar. But if given the
> following,
> >> then the simple answer dies.
> >
> > select Request from MyTable group by Request
> > having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;
>
> ... and sum(Description not in ['Foo', 'Bar']) = 0
>
> Add conditions to taste.
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811&amp;sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3D&amp;reserved=0
>
_______________________________________________
sqlite-users mailing list
[hidden email]
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811&amp;sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3D&amp;reserved=0
_______________________________________________
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 guidance

Joseph L. Casale
-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf
Of Roman Fleysher
Sent: Thursday, December 13, 2018 2:29 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Grouping guidance

> Hypothesis can never be proven. It can only be rejected with data
> contradicting it at hand.
>
> "..the quickest way ..." implies someone else corrects you.

Thanks a lot everyone, I appreciate the thorough insight (and humor)!

jlc
_______________________________________________
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 guidance

David Raymond
https://xkcd.com/2078/


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Joseph L. Casale
Sent: Thursday, December 13, 2018 5:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] Grouping guidance

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf
Of Roman Fleysher
Sent: Thursday, December 13, 2018 2:29 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Grouping guidance

> Hypothesis can never be proven. It can only be rejected with data
> contradicting it at hand.
>
> "..the quickest way ..." implies someone else corrects you.

Thanks a lot everyone, I appreciate the thorough insight (and humor)!

jlc
_______________________________________________
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