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]