compute by [altered date] ???
-
Hi,
Using SQL2K, can anyone make this work ? Whenever I try to compute by an altered date I get stuck...
What I want is this: (this works)
use northwind
go
select OrderDate, CustomerID, count(*) from dbo.Orders
group by OrderDate, CustomerID
order by OrderDate, CustomerID
compute sum(count(*)) by OrderDate
BUT using an converted date such as the following: (this fails)
use northwind
go
select convert(char(7),OrderDate,120), CustomerID, count(*) from dbo.Orders
group by convert(char(7),OrderDate,120), CustomerID
order by convert(char(7),OrderDate,120), CustomerID
compute sum(count(*)) by convert(char(7),OrderDate,120)
Result: Column 'dbo.Orders.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
'Rollup' does a good job but I prefer 'compute by' for appearance and instant readability.
Thanks for any help.
David
dgm -
Hi,
Try this.
select convert(char(7),OrderDate,120), CustomerID, count(*) from dbo.Orders
group by OrderDate, CustomerID
order by OrderDate
compute sum(count(*)) by OrderDate
I think it will generate what you want.
You do not need to specify the convert on the group by or the order by and the sum by I believe. Regards
Richard.
-
Hi David,
After re-reading your query I suddenly realised you where trying to calculate the number of orders for the month
Sorry for the bum answer in the previous post, try this one instead I believe it will return the answers you are looking for.
SELECT CONVERT(VARCHAR(7),OrderDate,120), CustomerID, COUNT(*) FROM dbo.Orders
GROUP BY OrderDate, CustomerID
ORDER BY 1, CustomerID
COMPUTE SUM(COUNT(*)) BY CONVERT(VARCHAR(7),OrderDate,120), CustomerID
You will notice the 1 in the order by clause. This is the column position within the select part of the statement.
Regards
Richard....
-
Spot on R|ichard, many thanks. I gave up using '1' when the Tandem went out of fashion...
Cheers,
David
dgm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply