Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Grouping problem

I am trying to get a table to display where my like rows would sum together,
but now matter how I do it there are 2 rows (in my example) that always show
as separate rows and I want to combine them.
For example:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
Should not show 0 0 0 0
Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
the 60 and 90 column should be on the same line.
This was done with the following statement:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (p1.PurchasedProductID = p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1
If I add the (ProductTypeID = 1) to the last line:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)
Then I get:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
This gets rid of the last line (which I wanted).
What I would like it to look like is:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 30 0 20 10
(5) 60-Day Posting 5 0 5 0
How can I make it do that?
I assume I have to group it, but I can't seem to make that work with these
subqueries. I get errors, such as you can't use a subquery in a group by
clause.
Here is the table and data (really cut down).
drop table PurchasedProducts
go
CREATE TABLE [dbo].[PurchasedProducts] (
[PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductTypeID] [int] NULL,
[ProductName] [varchar] (20) NULL ,
[PostingsLeft] [int] NULL ,
[DateExpires] [datetime] NULL
) ON [PRIMARY]
GO
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('30-Day Posting',1,1,'11/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,10,'1/24/2006')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,20,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(5) 60-Day Posting',1,5,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('Should not show',2,90,'12/01/2005')
go
Thanks,
TomThe easiest way to solve this is to group your results as illustrated below:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
- Peter Ward
WARDY IT Solutions
"tshad" wrote:

> I am trying to get a table to display where my like rows would sum togethe
r,
> but now matter how I do it there are 2 rows (in my example) that always sh
ow
> as separate rows and I want to combine them.
> For example:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> Should not show 0 0 0 0
> Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
> the 60 and 90 column should be on the same line.
> This was done with the following statement:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (p1.PurchasedProductID = p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1
> If I add the (ProductTypeID = 1) to the last line:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)
> Then I get:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> This gets rid of the last line (which I wanted).
> What I would like it to look like is:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 30 0 20 10
> (5) 60-Day Posting 5 0 5 0
> How can I make it do that?
> I assume I have to group it, but I can't seem to make that work with these
> subqueries. I get errors, such as you can't use a subquery in a group by
> clause.
> Here is the table and data (really cut down).
> drop table PurchasedProducts
> go
> CREATE TABLE [dbo].[PurchasedProducts] (
> [PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductTypeID] [int] NULL,
> [ProductName] [varchar] (20) NULL ,
> [PostingsLeft] [int] NULL ,
> [DateExpires] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('30-Day Posting',1,1,'11/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,10,'1/24/2006')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,20,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(5) 60-Day Posting',1,5,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('Should not show',2,90,'12/01/2005')
> go
>
> Thanks,
> Tom
>
>|||"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:89A22616-2111-4C1B-9D6D-F9AF452FB238@.microsoft.com...
> The easiest way to solve this is to group your results as illustrated
below:
Does it!!
You just treated my select as another table. I can never seem to come up
with that myself. I always understand it when I see it, but I can't seem to
see it when I need it.
Not really sure of the thought process to come up with it.
I was almost there, but couldn't quite see it.
Thanks,
Tom
> SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
> SUM(days60) AS [60], SUM(days90) AS [90]
> FROM (select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> Days30 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> Days60 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> Days90 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)) AS a
> GROUP BY productname
>
> - Peter Ward
> WARDY IT Solutions
> "tshad" wrote:
>
together,
show
in
these
by
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping for Sum

My query:

SELECT Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name], SUM(Ticket.Barrels) AS Expr1
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP BY Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

I would like to produce a table as follows:

RRC Number - Lease Name - Field Name -Sum of Barrels

0001 - Lease1 - Field1 - 120

0002 - Lease1 - Field3 - 340

0002 - Lease2 - Field3 - 120

Instead I have some of the data on several rows:

0001 - Lease1 - Field1 - 70

0001 - Lease1 - Field1 - 50

0002 - Lease1 - Field3 - 40

0002 - Lease1 - Field3 - 300

and so on ....

What am I doing wrong?

You need to clean up both the select columns and group by columns. I can see the Ticket.Barrels coumn included in both select and group by which is the reason for what you got. Try another on with this column and other unnecessary columns.
.
SELECT Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name], SUM(Ticket.Barrels) AS 'Sum of Barrels'
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP

BY Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

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 test
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...
>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]
[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...
> 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)

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)

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...
>>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
>|||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...
>> 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...
>>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
>>
>|||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...
>> 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...
>>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
>>
>>
>|||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...
>> 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...
>> 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...
>>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
>>
>>
>>
>|||after adding the 3,1,1 set the data looks like '
>> 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,4)
>> insert into test values(2,4,5)
>> insert into test values(2,5,9)
>> insert into test values(3,1,1)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,4)
>> insert into test values(3,4,7)
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...
> 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...
>> 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...
>> 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...
>>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
>>
>>
>>
>>
>|||On Wed, 20 Apr 2005 23:50:35 -0400, D wrote:
>after adding the 3,1,1 set the data looks like '
>>> 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,4)
>>> insert into test values(2,4,5)
>>> insert into test values(2,5,9)
>>> insert into test values(3,1,1)
>>> insert into test values(3,1,2)
>>> insert into test values(3,2,3)
>>> insert into test values(3,3,4)
>>> insert into test values(3,4,7)
>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)|||thanks ALOT!! This works really good.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vv1n61pnl9tr20phih2mg4n0ps4ml16226@.4ax.com...
> 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)sql