tough sql statement help please

Howdy - and thank you in advance for any help!!! 
Here is what I have so far:

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:
SalesPersonName
g.goaltype
goalacquired
g.goalamount
g.beginingdate
g.endingdate

0
tnichols333
11/25/2009 3:32:29 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
1015 Views

Similar Articles

[PageSpeed] 29

What is the version of SQL Server are you using?


"tnichols333" <u56470@uwe> wrote in message news:9fa5eb2632a73@uwe...
> Howdy - and thank you in advance for any help!!!
> Here is what I have so far:
>
> 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:
> SalesPersonName
> g.goaltype
> goalacquired
> g.goalamount
> g.beginingdate
> g.endingdate
> 


0
Uri
11/25/2009 3:40:27 PM
2005 - thanks for any help you can provide!!!

Uri Dimant wrote:
>What is the version of SQL Server are you using?
>
>> Howdy - and thank you in advance for any help!!!
>> Here is what I have so far:
>[quoted text clipped - 29 lines]
>> g.beginingdate
>> g.endingdate

0
tnichols333
11/25/2009 4:14:02 PM
When you use aggregate functions you have to use the GROUP BY clause to list the grouping columns. Try the following 
query (notice the predicates on the Customer table are moved from the WHERE clause to the JOIN conditions, otherwise 
they turn the outer join to inner join):

SELECT sp.FirstName + ' ' + sp.LastName AS SalesPersonName,
        g.goaltype,
        g.goalamount,
        g.beginingdate,
        g.endingdate,
        SUM(CASE WHEN g.goaltype = 'customeramount' THEN 1 ELSE 0 END) AS GoalAcquired,
        SUM(CASE WHEN g.goaltype = 'grossprofit' THEN c.grossprofit ELSE 0 END) AS GrossProfit
FROM Goal AS g
INNER JOIN SalesPerson AS sp
   ON g.SalesPersonId = sp.SalesPersonId
LEFT JOIN Customer AS c
   ON sp.SalesPersonId = c.SalesPersonId
  AND c.Acquired = 'Yes'
  AND CONVERT(SMALLDATETIME, c.AcquiredDate, 101) BETWEEN g.BeginingDate AND g.EndingDate
WHERE g.BeginingDate < DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010102')
   AND g.EndingDate  >= DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010101')
GROUP BY sp.FirstName + ' ' + sp.LastName,
          g.goaltype,
          g.goalamount,
          g.beginingdate,
          g.endingdate;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
11/25/2009 4:54:26 PM
wow... what it is doing...

results:
name  goalid  goaltype              goalamount  bdate      edate
goalacquired  grossprofit
Brad    1        customeramount   20              10-1-09   10-31-09   1
0
John    2        customeramount   25              10-1-09   10-31-09   1
0

what they should be:
name  goalid  goaltype              goalamount  bdate      edate
goalacquired  grossprofit
Brad    1        customeramount   20              10-1-09   10-31-09   0
0
John    2        customeramount   25              10-1-09   10-31-09   2
0

not quite sure why it is doing that???

Plamen Ratchev wrote:
>When you use aggregate functions you have to use the GROUP BY clause to list the grouping columns. Try the following 
>query (notice the predicates on the Customer table are moved from the WHERE clause to the JOIN conditions, otherwise 
>they turn the outer join to inner join):
>
>SELECT sp.FirstName + ' ' + sp.LastName AS SalesPersonName,
>        g.goaltype,
>        g.goalamount,
>        g.beginingdate,
>        g.endingdate,
>        SUM(CASE WHEN g.goaltype = 'customeramount' THEN 1 ELSE 0 END) AS GoalAcquired,
>        SUM(CASE WHEN g.goaltype = 'grossprofit' THEN c.grossprofit ELSE 0 END) AS GrossProfit
>FROM Goal AS g
>INNER JOIN SalesPerson AS sp
>   ON g.SalesPersonId = sp.SalesPersonId
>LEFT JOIN Customer AS c
>   ON sp.SalesPersonId = c.SalesPersonId
>  AND c.Acquired = 'Yes'
>  AND CONVERT(SMALLDATETIME, c.AcquiredDate, 101) BETWEEN g.BeginingDate AND g.EndingDate
>WHERE g.BeginingDate < DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010102')
>   AND g.EndingDate  >= DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010101')
>GROUP BY sp.FirstName + ' ' + sp.LastName,
>          g.goaltype,
>          g.goalamount,
>          g.beginingdate,
>          g.endingdate;
>

0
tnichols333
11/25/2009 5:26:25 PM
If you post CREATE TABLE statements for all tables involved, INSERT statements with sample data for each table, and 
expected result set based on the data, someone can test and figure out what adjustments can be made to produce correct 
results.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
11/26/2009 1:23:51 AM
Reply:

Similar Artilces:

Outlook Help #7
2 Outlook questions How can I get appointments to automatically roll from one day to the next? How can I import calendar info from Outlook to Works calendar? ...

If statements #2
I have this formula which works 99% of the time, only in the last instance do I get it to fail when I add if T<>""),"". ............O...............Q..................R.............S...................T 1) ........X 2) ........X...................................................X 3) ........X................X 4) ........X................X...................X 5) .......X.................X...................X.............X 6) .........X................X....................X..............X................X What I'm trying to do is create a formula so that the user ...

Deadlock on SQL SELECT statement
I have inherited the maintenance of a product which includes the snipet of code below. Every 10 seconds the code is executed. It is causing a deadlock in some instances, but I am undable to reproduce the problem on my machine. The "PC" table contains a list of PCs seen on a network, so isn't very large. Since I dont have much background in database programming, I was wondering if there is some simple answer to the deadlock issue...but from reading on deadlocks, there rarely seems to be a simple solution. //**************************************** // Find PCs to restart CStri...

SORTING HELP!
If I sort my table alphanumerically by one of the columns in the middle, how to I make sure all the info in their respective rows follows them? I need to alphabetize my document by information in the 5th row, but when I do that, the rest of the information gets all out of order, how do I get it to sort that way without loosing the corresponding info in the rows? Thanks Hi, If I didn't misundeerstand when sorting select the hole range let' say from column A to E then sort by column E if you have formulas in some to the columns you will have to overwrite them copy an...

Stacked Column Charts help!
I have a stacked column chart for some accounts I have and I'm trying to add a YTD total at the end. I have 7 different accounts that are stacked going from jan to dec, and then the last column on the right is YTD. I added the YTD by adding a column in my data with the sums of the monthly data, but the YTD column is now making it hard to read my monthly data because the axis is larger. I tried adding a secondary axis, but that changes the whole data series, not just the YTD data points. Any suggestions are greatly appreciated. ...

Preventing Duplicate Data Entries /Please email me @ Zigball @ Gmail.com VBA Problems!
Hello microsoft.public.excel my name is Zig I am trying hard to find out how I can use a input application to input data into a specified excel sheet. I have learned how to input the data into the excel sheet although I need to prevent the inputs from being duplicated. I have used a validating solver to prevent duplicate entries but it only works if you type the text into the sheet. I am unable to get the input application to follow the validation rule. Is there a way that I can use a input application to prevent duplicate entries into the excel sheets and if duplicate data is true can i redir...

help with IF THEN
What is the correct syntax to write a statement that, in english, does this If cell L4 = 0 Then delete cells K4 through M4 and shift the cells up Better yet can something be written that checks all L cells for the 0 value and if it finds 0 then deletes the adjacent K through M cells and shifts all cells up TIA ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Help converting Money 99 files to Money 2006
Can anyone help me convert files created in Money 99 into the new Money 2006 that I just payed for today? I've got so much information in the Money 99 and I really don't want to lose it all or have to recreate it all. Here are the error messages that I get: When the program first opens and tries to access the old files: "'File Name' may be corrupted or is not a Money file. If you chose the correct file and it would not open, you can try to repair this file or restore a recent backup file. To do this, click OK and then follow the instructions on the screen. I...

Can the retail management software be used to help build an e-com.
I am about to open a retail store and also want to set up a web site. Is there the capability of integrating info from the retail files into the website? New West technology has a great package "down in new orleans" <down in new orleans@discussions.microsoft.com> wrote in message news:97A30580-A242-43DB-89FF-EF44FE70A7A1@microsoft.com... >I am about to open a retail store and also want to set up a web site. Is > there the capability of integrating info from the retail files into the > website? www.nitroasl.com "down in new orleans" <down in new...

Help with VBA (open query)
I have one command button used to open a form. How can I change if I wan to open a query instead of form? I try with this but it doesn't works. Something wrong with the code? Or I use the wrong method? #### strQuery = "grouping" (query name) DoCmd.openquery strQuery, , , strSQL #### Private Sub openform_Click() Dim strSQL As String Dim strWhere As String If Not IsNull(tool_id) Then strWhere = strWhere & " AND tool_id= " & "'" & tool_id & "'" End If If Not IsNull(cboStatus) Then strWhere = strWhere & " AND status= &...

help 08-18-03
Ok I have 3 computers 2 have windows xp the other has windows 98se. I am networking them together with a hub. The xp machines see each other and the win 98 machines see the xp machines but the 98 machine won't see it self and the xp machines won't see it. I have a\enabled file sharing on all computers and from what I can tell all the portocols are there except xp doesn't use NEt beui like win98. The netbios is enabled over ipx\spx ...

If u have OWA in your network
Hi i'm struggling with setting up my OWA. 1) When installing a certificate i only get the option of "preparing now and sending it later...", the .."send immediately..."option is greyed out. I did install a CA on the current server. Any services that i must restart.I've allready restart IIS Admin but nothing change. In creating a new certificate i go to IIS manager - Default website - what is wrong..... 2)I can access my OWA mailbox from another pc on the LAN by typing in http://servername/exchange/mailboxname and then i must just put in the password obviousl...

Excel and SQL Server
I need to populate a column in Excel from SQL Server based on data in an existing Excel column. For example if I have a spreadsheet with employee ID’s in a column I need to go to SQL Server and populate the employee names associated with the Employee ID’s into an adjoining column. I have found plenty of articles on populating a spreadsheet from SQL Server but nothing on how to use an existing column as part of the join. I appreciate any help I can get. hi you need to declare your excel column a named range then use the excel file as a data source in your guery joining the ...

[Please help] 3 failed update installation in Win 7
Those three failed installation are with error code: 643 1. Security Update for Microsoft Visual C++ 2008 Redistributable Package (KB973924); 2. Security Update for Microsoft XML Core Services 4.0 Service Pack 2 (KB954430); and 3. Update for Microsoft XML Core Services 4.0 Service Pack 2 (KB973688). I used a toshiba satellite l505d-s5983. Please anyone can help me... Thank you in advance. You receive error code 0x80070643 or error code 0x643 when you use the Windows Update or Microsoft Update Web sites to install updates http://support.microsoft.com/kb/958052 You can ...

Error Converting Secure Files to 2003....PLEASE HELP!!!
I have a Secure Access Database that I created in Access 2000, when I installed Office 2003 it no longer works and crahes Access. I've also tried converting the file using the convertion tool but I get an error that I don't have permission to make changes. I can open the file long enough to get to the switchboard but if I click on an item that opens a report or query .....Access crashes. Also, when I open the 2000, most of the menu items that would normally be there are not. Please, if someone has heard of this and knows how to fix it I would greatly appreciate it. Thanks Dave ...

Access 2000 DB import tables in SQL Server 2000 DB
I inherited an Access 2000 database that needs to have tables imported into a SQL Server 2000 new database. The Access database has approximately 30 tables. The Access database has approximately 10 forms, 10 reports, and 10 modules. I would like to import Access database tables into the new SQL Server database. After the tables has been imported I will create the relationship between the tables. Next I will create the indexes and link the SQL Server tables to Access database. I know Access 2000 upsizing will complete this procedure but it must have SQL Server v7.0 or v6.X database....

help me #4
hi, I'm newer to c++ .I just want to display a dialog box after 20 seconds.so please if any one knows the function which responsible for these matter will be great fady.jo@gmail.com wrote: > hi, > I'm newer to c++ .I just want to display a dialog box after 20 > seconds.so please if any one knows the function which responsible for > these matter will be great > SetTimer causes WM_TIMER messages to be sent periodically. Then add a message handler function for WM_TIMER. And, call KillTimer when you want the periodic messages to stop. -- Scott McPhillips [VC++ MVP] S...

Mistake, please help !
Hi all, System: Exchange 2003 on Windows 2003 Here is my problem, I was changing the Permissions configuration on an Global Address List named "CLIENT address list" and I have created an entry for the authenticated users group with the full control DENY permissions. And now I can't see this "Client address list" anymore under Exchange system manager. Because the domain admin is also an authenticated user I can't see this GAL. Under ADSIedit I can see the name of this Global address list but that's it, I can't open it or delete it.... My question is...

Help with .pst file
My outlook.pst file for Outlook 2000 is 600 meg. That's far too much after exporting only 17 meg from OE6. How do I, and what program should I use to, open the .pst file in Windows Explorer so I can see what is going on and why the file is so big. Thanks again for your help, Dan No other program but Outlook can open a PST file. Did you compact it? -- Russ Valentine [MVP-Outlook] "Daniel Arrepas" <arrepas.daniel@butler-rosebury.com> wrote in message news:g3p0e.60$pA4.499@news.uswest.net... > My outlook.pst file for Outlook 2000 is 600 meg. That's far too muc...

X axis label help
I have created a line chart with four values from cells in sheet 1, sa A1:D1. Each data point corresponds to a different material. Th material names are in cells in sheet 1, say A2:D2. I select those cell with the material names as my category (X) axis labels. The materia names in the cells have underlining and subscripts, however thi formatting does not appear on the chart. How can I get the underlinin and subscripts to appear as they do in the cells on sheet 1? Thank yo for the help -- Message posted from http://www.ExcelForum.com Hi AFAIK you can't do this as the formulas only link v...

Get help with Mail
In the Help folder should I get help with Get help with Mail. But the web address seems not to be correct. Is there anybody who knows how I can get back the help function? It's broken at the moment. There's a lot going on involving lots of different teams, and co-ordination between them is not exactly 100%. I'm not quite sure what you mean by the 'help folder', but to access the help that's available (for what it's worth), go to http://help.live.com/help.aspx?project=wl_mailv3 You could also try http://windowslivehelp.com/solutions.aspx?productid=15 ...

Help Printing RTF without windows Theme background.
I'm writing an application that prints out the contents of an RTF file. Problem is, if (at least in Vista), you go to the Personalize Desktop dialog and change the Window Color and Appearance to High Contract Black (through "Open classic appearance properties for more color options"), it causes the background color of my printouts to be black too! Is this by design? I want my printouts to be black text on white paper regardless of what I set my monitor and GUI to. I've reproduced this using MS Sample application WordPad in VS2005 found here: http://msdn2.microsoft.c...

RMS 1.2 SQL 2005 compatible?
Is RMS 1.2 compatible with SQL 2005? (full SQL, need the larger than 4GB database support) Although it is not supported my Microsoft, we have been able to get it to work. You will not be able to use Administrator to manage the database, nor can you do a backup from either Manager or Administrator. You'd have to do that through SQL Management Studio Express, but once the database is up and running you should be able to run RMS off of it. Good luck. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-2...

How to backup reports created using SQL Reporting Services?
Can anyone please tell me how do I take backups of reports created through SQL Reporting Services (SSRS). Does anyone have documentation on procedure of backing up reports created with SQL Reporting Services? Also does anyone have steps on how to restore reports from backup? I have been assigned task to modify some reports using SQL Reporting Services. It is my first time working with SSRS reports, so I want to be sure to take a good backup of all the reports created so far using SSRS and I'd like to be able to know how to restore a report from backup in case something goes wrong....

Money 2001
I have recently moved to Papua New Guinea and have set up my local accounts on Money 2001. I have added the local currency - PGK kina to the currencies but when I try to download my bank statements I get this error box message: Import Completed Your file import was completed, but 1 item could not be processed... File import X Your statement contained a default currency (PGK) which was unrelated to the currency of the account "Westpac PNG Cheque". Money does not support statements containing unrelated default currencies. I was dowloading the file in OFX format. QIF files work...