# 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)