grouping / count question

I am trying to obtain a sum of the various sequences from the following
table. I was thinking I could do some sort of select sum( <union query
here> ) but that's not the case.
so for the following sample I would like to count the number of times the
values 1 & 2 occur in the same testsets.
Thanks
create table test (testset int, testnumber int, value int )
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
--select testset from test where value = 1 and value = 2 group by testset
select testset as t from test where testnumber = 1 and value = 1
union
select testset as t from test where testnumber = 2 and value = 2
drop table testSELECT testset
FROM test
WHERE testnumber = value
AND testnumber IN (1,2)
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am trying to obtain a sum of the various sequences from the following
>table. I was thinking I could do some sort of select sum( <union query
>here> ) but that's not the case.
> so for the following sample I would like to count the number of times the
> values 1 & 2 occur in the same testsets.
> Thanks
> create table test (testset int, testnumber int, value int )
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> --select testset from test where value = 1 and value = 2 group by testset
> select testset as t from test where testnumber = 1 and value = 1
> union
> select testset as t from test where testnumber = 2 and value = 2
> drop table test
>|||Thank you but I am trying to sum the total number of times that 1 and 2 have
come out. Optimally I'd like to be able to sum any number of combinations
from within the same testset. For example how many times has the sequence
1,2 and 7 come out? The ultimate would be something that told me the top
sequence is 1,2 & 7 with 55 occurrences.
Thanks
Regards
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> SELECT testset
> FROM test
> WHERE testnumber = value
> AND testnumber IN (1,2)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Can you give the expected results for your questions with the sample data
you have given (Add more data if necessary)? Also, what is the Primary Key
on the table 'test'?
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thank you but I am trying to sum the total number of times that 1 and 2
> have come out. Optimally I'd like to be able to sum any number of
> combinations from within the same testset. For example how many times has
> the sequence 1,2 and 7 come out? The ultimate would be something that told
> me the top sequence is 1,2 & 7 with 55 occurrences.
> Thanks
> Regards
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>|||for the sample results if I were searching for the total of the number of
occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
For the primary key I have testset and testnumber
Thanks for your help today
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
> Can you give the expected results for your questions with the sample data
> you have given (Add more data if necessary)? Also, what is the Primary Key
> on the table 'test'?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>|||Please provide the complete expected resultset, with column names and
values. That will make it a lot clearer for me than a textual description.
Also, what will the resultset be if you add the row insert into test
values(3,1,1) to the test data?
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
> for the sample results if I were searching for the total of the number of
> occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
> For the primary key I have testset and testnumber
> Thanks for your help today
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
>|||after adding the 3,1,1 set the data looks like '

[vbcol=seagreen]
if I could get a result set that looked like
x y total
1 2 3
3 4 2
4 5 2
I started with sets of 2 and figured once I have that I can expand it to 3
or 4 if needed to so that the results would look like
x y z total
1 2 3 2
2 3 4 2
If I could get that, that would be most excellent. Basically I'm trying to
determine what patterns (grouped by testsets) occur the most. And the value
never repeats within a testset.
Thanks alot.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eUCV39cRFHA.252@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Please provide the complete expected resultset, with column names and
> values. That will make it a lot clearer for me than a textual description.
> Also, what will the resultset be if you add the row insert into test
> values(3,1,1) to the test data?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
>|||On Wed, 20 Apr 2005 23:50:35 -0400, D wrote:

>after adding the 3,1,1 set the data looks like '
>
>
>if I could get a result set that looked like
>x y total
>1 2 3
>3 4 2
>4 5 2
>I started with sets of 2 and figured once I have that I can expand it to 3
>or 4 if needed to so that the results would look like
>x y z total
>1 2 3 2
>2 3 4 2
>If I could get that, that would be most excellent. Basically I'm trying to
>determine what patterns (grouped by testsets) occur the most. And the value
>never repeats within a testset.
Hi D,
If I understand you correctly, you need a technique known as relational
division. Try if the following code helps:
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (value int not null primary key)
insert into wanted values (1)
insert into wanted values (2)
go
SELECT test.testset
FROM test
INNER JOIN wanted
ON test.value = wanted.value
GROUP BY test.testset
HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
go
drop table wanted
drop table test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks. It does work but I'm wondering how I can make it more flexible in
terms of the wanted values. Let's say I have a couple hundred different
combinations of 3 numbers, I wonder how would I cycle through that.
Another way I just figured out is to use a self join
select distinct a.testset from test a, test b where a.testset = b.testset
and a.value = 1 and b.value = 2
Thanks for your help

> Hi D,
> If I understand you correctly, you need a technique known as relational
> division. Try if the following code helps:
> create table test (testset int, testnumber int, value int,
> primary key(testset, testnumber),
> unique(testset, value))
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> go
> create table wanted (value int not null primary key)
> insert into wanted values (1)
> insert into wanted values (2)
> go
> SELECT test.testset
> FROM test
> INNER JOIN wanted
> ON test.value = wanted.value
> GROUP BY test.testset
> HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
> go
> drop table wanted
> drop table test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 07:29:09 -0400, D wrote:

>Thanks. It does work but I'm wondering how I can make it more flexible in
>terms of the wanted values. Let's say I have a couple hundred different
>combinations of 3 numbers, I wonder how would I cycle through that.
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go

>Another way I just figured out is to use a self join
>select distinct a.testset from test a, test b where a.testset = b.testset
>and a.value = 1 and b.value = 2
Yeah, but the problem with that approach is that you have to increase the
number of joins as the number of values you want to find goes up. Imagine
what the query would look like if you have to find all testsets where all
of the values 1 through 10 are present - you'd have to join the same table
tenfold!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)