compute by [altered date] ???

• dgm

  SSC Journeyman

  Points: 75

  More actions

  August 10, 2004 at 7:33 am

  #108485

  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

• Richard Clarke-156683

  SSC Enthusiast

  Points: 190

  More actions

  August 10, 2004 at 7:48 am

  #518309

  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.

   

• Richard Clarke-156683

  SSC Enthusiast

  Points: 190

  More actions

  August 10, 2004 at 8:06 am

  #518320

  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....

• dgm

  SSC Journeyman

  Points: 75

  More actions

  August 10, 2004 at 8:38 am

  #518335

  Spot on R|ichard, many thanks. I gave up using '1' when the Tandem went out of fashion...

   

  Cheers,

  David

  
  dgm