MSSQL query problems

[fRAiLtY-]

Hi,

 

Here's what I'm trying to do, I was told by a PHP guy that SQL was the better more efficient way of doing this, but having only limited experience with MSSQL I'm finding it hard to get the logic right. I need to select a customer, get his orders, group them by month and year and end up with a monthly average, 1 figure. So MemberX has been a member since January 2011 and since that point his average monthly sales are £44553.44 or whatever.

 

Here's the query I have at the moment.

 

SELECT YEAR(CreateDateTime) AS 'Year', MONTH(CreateDateTime) AS 'Month', CreatedDate, Name, Code, AVG(PriceEst)
FROM MainJobDetails
INNER JOIN Customers ON MainJobDetails.InvoiceCustomerCode = Customers.Code
WHERE Customers.Code LIKE  'APP004'
GROUP BY YEAR(CreateDateTime), MONTH(CreateDateTime)

 

APP004 is the customers unique code, CreateDateTime is the timestamp the order was placed, PriceEst is the price of the order, CreatedDate is when the Customer was created. So I need, I think, to get a total of PriceEst from CreatedDate -> now, and get an average figure.

 

The problem is I get odd errors from MSSQL, like this:

 

ERROR: Column 'MainJobDetails.CreateDateTime' is invalid in the
select list because it is not contained in either an aggregate
function or the GROUP BY clause.
Error Code: 8120

 

I don't really understand what it means, I'm using CreateDateTime in the GROUP BY clause, clearly?

 

Any help is appreciated.

 

Cheers.

[Muddy_Funster]

You need to refference the field directly in the group by, don't bother with the fact that you are extracting the Year and Month from it in the select, just group by the field.  Having said that, it will get rid of that specific error, but I have a suspicion that won't get you the result set that you are looking for.

 

Group By in MSSQL is a bit convoluted when compaired to MySQL, you would be well set to reasearch the function in deapth.  You have to explicitly name every field that does not have and agrigate function run against it (ie SUM / AVG / STDEV / MIN / MAX etc) in order.