Howdy - and thank you in advance for any help!!! Here is what I have:SELECT sp.FirstName + ' ' + sp.LastName AS SalesPersonName, g.*, COUNT(CustomerId) AS GoalAcquired FROM Goal g INNER JOIN SalesPerson sp ON g.SalesPersonId = sp.SalesPersonId LEFT JOIN Customer c ON sp.SalesPersonId = c.SalesPersonId WHERE c.Acquired = 'Yes' AND g.BeginingDate <= GetDate() AND g.EndingDate >= GetDate() AND CONVERT(smalldatetime, c.AcquiredDate, 101) BETWEEN g.BeginingDate AND g.EndingDate
This is the error I get. Column 'SalesPerson.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Basically I am trying to list ALL goals where we are in the timeframe of the beginingdate and endingdate, then the goalacquired field should be populated with there current status, if it is nothing I would like to put 0. Just thought of one caviat... I need the count(customerid) if g.goaltype = 'customeramount' and I need sum(c.grossprofit) if g.goaltype = 'grossprofit'. Wow - this is a hard sql statement!!!Expected outcome would be:SalesPersonNameg.goaltypegoalacquiredg.goalamountg.beginingdateg.endingdate
sql error / help with sql query with aggragate fun
Posted - 2009-11-25 : 10:09:45
