How to get around the error on aggregate function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get around the error on aggregate function?

Author

Topic

Almir_7
Starting Member

14 Posts

Posted - 2008-11-10 : 12:51:23

Hi all.

I'm trying to execute a select top 1 * query to copy all the columns to the temp table and also only select one of potentialy duplicate rows in my original staging table. See bellow.

SELECT TOP 1 *
INTO temp_stage_table --temp table
FROM WS_Survey_Staging_DB --stage table
WHERE Audit_created_by <> 'AR_Escalator'
GROUP BY Log_ID
ORDER BY Audit_status_new, Audit_created_date DESC

I'm grouping by Log_id as the rows are going to be different as per the audit status and the created data of each audit entry. So in this example i might have the status changing on the ticket more then once and i want to capture the last on. I'm already weeding out the different statuses in my query on the source table.

When i execute the above query i get the following error:
invalid in the select list because it is not contained in either an aggregate function or the group by

If i include all the columns in the group by then the returned data is not correct. Because each row is then groupped by on more then one column and the end result does not bring back the correct occurance in the audit log.

Second reason for having the temp table is so that i can execute a few update queries on the data before sending everything back to the stage table for rest of processing steps.

Any ideas appreciated.
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-10 : 13:30:10

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Almir_7
Starting Member

14 Posts

Posted - 2008-11-10 : 14:54:31

This is the top query where i pull all the rows from the ticketing database which includes the multiple rows for audit entries. I only pull last 4 days worth of data.

************************************************************
declare @StartDate datetime
set @StartDate = Convert(VarChar, GetDate() - 4, 101)
declare @EndDate datetime
set @EndDate = Convert(VarChar, GetDate(), 101)

select

hd.Incident_Number as [Log_ID],
dateadd(ss,hd.Reported_Date,'01-01-1970') as [Opened_Date],
dateadd(ss,hd.Closed_Date,'01-01-1970') as [Closed_Date],
hd.Assignee as [Specialist_Name],
hd.First_Name+' '+hd.Last_Name as [Customer_Name],
hd.Internet_E_Mail as [Customer_Email],
hd.BPO+hd.Company+hd.Categorization_Tier_1 as [Client_Name],
hd.Description as [Short_Description],
p.Primary_Language as [Lang_code],
hd.BPO as [Family],
p.Corporate_ID as [Employee_ID],
hd.Assignee_Login_ID as [Enterprise_ID],
z.Site_Country [Call_Center],
p.Site_Country as [Customer_Country],
p.Region as [Customer_Region],
hd.Assigned_Support_Organization as [Support_Organization],
hd.Assigned_Group as [Assignee_Support_Group],
hd.BPO as [BPO],
z.Site as [Site],
hd.Site as [Customer_Site],
hd.Categorization_Tier_1 as [Tier1],
hd.Categorization_Tier_2 as [Tier2],
hd.Categorization_Tier_3 as [Tier3],
hd.Resolution_Category as [Resolution_Category],
hd.entry_id as Entry_ID,
ha.created_by as Audit_Created_By,
ha.status_new as Audit_Status_New,
dateadd(ss,ha.created_date,'01-01-1970') as [Audit_Created_Date]
from
dbo.HPD_Help_Desk hd with (nolock)
left join dbo.CTM_People z with (nolock) on hd.Assignee_Login_ID = z.Remedy_Login_ID
inner join dbo.CTM_People p with (nolock) on hd.Person_ID = p.Person_ID
inner join dbo.bss_hpd_help_desk_audit ha with (nolock) on hd.entry_id = ha.entry_id
where
hd.Closed_Date is not null AND p.Do_Not_Survey is null AND hd.Do_Not_Survey is null AND hd.Internet_E_Mail is not null AND hd.Internet_E_Mail like '%@%' AND dateadd(ss,hd.Closed_Date,'1970-01-01') between @StartDate + ' 00:00:00.000' AND @EndDate + ' 23:59:59.999' AND hd.Status = '5' AND ha.status_new >= '4' AND hd.Assignee is null

order by closed_date
*************************************************************************

This is what the stage table looks like. Column names that are commented are how the sample data is shown at the bottom of the page to fit the dimensions:

CREATE TABLE [New Table] (
[Log_ID] nvarchar (15) NULL, --Log_id
[Opened_Date] datetime NULL, --O_Date
[Closed_Date] datetime NULL, --C_Date
[Specialist_Name] nvarchar (69) NULL, --S_Name
[Customer_Name] nvarchar (61) NULL, --C_Name
[Customer_Email] nvarchar (255) NULL, --C_Email
[Client_Name] nvarchar (569) NULL, --not shown
[Short_Description] nvarchar (100) NULL, --S_Desc
[Lang_code] nvarchar (255) NULL, --Lang
[Family] nvarchar (255) NULL, --not shown
[Employee_ID] nvarchar (30) NULL, --not shown
[Enterprise_ID] nvarchar (254) NULL, --not shown
[Call_Center] nvarchar (60) NULL, --not shown
[Customer_Country] nvarchar (60) NULL, --not shown
[Customer_Region] nvarchar (60) NULL, --not shown
[Support_Organization] nvarchar (60) NULL, --not shown
[Assignee_Support_Group] nvarchar (60) NULL, --not shown
[BPO] nvarchar (255) NULL, --not shown
[Site] nvarchar (60) NULL, --not shown
[Customer_Site] nvarchar (60) NULL, --not shown
[Tier1] nvarchar (60) NULL, --not shown
[Tier2] nvarchar (60) NULL, --not shown
[Tier3] nvarchar (60) NULL, --not shown
[Resolution_Category] nvarchar (60) NULL, --not shown
[Entry_ID] nvarchar (15) NOT NULL, --entry_id
[Audit_Created_By] nvarchar (30) NOT NULL, -- A_created_by
[Audit_Status_New] int NULL, --A_Status_New
[Audit_Created_Date] datetime NULL ) -- A_C_Date

Here is the sample data that sits in the stage table when i get to the step to execute the query my question is about.

Log_id O_Date C_Date Prod S_Name C_Name C_Email Short_Desc Lang entry_id A_created_by A_Status_new A_C_Date
INC1748193 11/2/2008 14:11 11/6/2008 21:20 1-Generic S_Name C_Name C_Email Test Eng INC1286318 AR_ESCALATOR 5 11/6/2008 21:21
INC1748193 11/2/2008 14:11 11/6/2008 21:20 1-Generic S_Name C_Name C_Email Test Eng INC1286318 Specialist1 4 11/3/2008 20:18
INC1750172 11/3/2008 16:45 11/7/2008 21:28 1-Wyeth S_Name C_Name C_Email Test Eng INC1287840 Specialist2 4 11/3/2008 22:04
INC1750172 11/3/2008 16:45 11/7/2008 21:28 1-Wyeth S_Name C_Name C_Email Test Eng INC1287840 AR_ESCALATOR 5 11/7/2008 21:31
INC1332047 8/5/2008 22:35 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC967140 AR_ESCALATOR 5 11/9/2008 1:31
INC1758837 11/4/2008 21:57 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC1294788 AR_ESCALATOR 5 11/9/2008 1:26
INC1763574 11/5/2008 16:18 11/9/2008 1:22 AHRS-G S_Name C_Name C_Email Test Eng INC1298334 AR_ESCALATOR 5 11/9/2008 1:23
INC1758837 11/4/2008 21:57 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC1294788 Specialist3 4 11/4/2008 22:10
INC1332047 8/5/2008 22:35 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC967140 Specialist4 4 11/5/2008 16:28
INC1763574 11/5/2008 16:18 11/9/2008 1:22 AHRS-G S_Name C_Name C_Email Test Eng INC1298334 Specialist5 4 11/5/2008 22:03
INC1746050 11/3/2008 6:00 11/10/2008 1:32 APS-E S_Name C_Name C_Email Test Ro INC1285084 Specialist6 4 11/6/2008 8:22
INC1765192 11/5/2008 21:11 11/10/2008 1:32 AHRS-G S_Name C_Name C_Email Test Eng INC1299836 Specialist5 4 11/5/2008 21:59
INC1771879 11/6/2008 20:41 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305044 Specialist7 4 11/6/2008 21:05
INC1765192 11/5/2008 21:11 11/10/2008 1:32 AHRS-G S_Name C_Name C_Email Test Eng INC1299836 Specialist5 4 11/6/2008 22:30
INC1772344 11/6/2008 21:23 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305470 Specialist8 4 11/7/2008 0:00
INC1772344 11/6/2008 21:23 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305470 AR_ESCALATOR 5 11/10/2008 1:32
INC1771879 11/6/2008 20:41 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305044 AR_ESCALATOR 5 11/10/2008 1:32
INC1765192 11/5/2008 21:11 11/10/2008 1:32 AHRS-G S_Name C_Name C_Email Test Eng INC1299836 AR_ESCALATOR 5 11/10/2008 1:35
INC1746050 11/3/2008 6:00 11/10/2008 1:32 APS-E S_Name C_Name C_Email Test Ro INC1285084 AR_ESCALATOR 5 11/10/2008 1:37

In my query i want to use the Log_id column to get only one occurance of each ticket in my final table. The select top1 should get me the last updated row based on the A_C_Date column. Which represents the Audit_Created_Date

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 16:21:00

use group by, max(id) and order by correctly ... it should work!

Almir_7
Starting Member

14 Posts

Posted - 2008-11-10 : 17:24:48

The expected output needs to be like this. All the rows where the Audit_update_by column is equal to AR_Escalator and the row with ticket number INC1765192 and audit time stamp of 21:59 because there is a later audit entry for the same ticket number right bellow and the time stamp is bigger ie. 22:30

Log_id O_Date C_Date Prod S_Name C_Name C_Email Short_Desc Lang entry_id A_created_by A_Status_new A_C_Date
INC1748193 11/2/2008 14:11 11/6/2008 21:20 1-Generic S_Name C_Name C_Email Test Eng INC1286318 Specialist1 4 11/3/2008 20:18
INC1750172 11/3/2008 16:45 11/7/2008 21:28 1-Wyeth S_Name C_Name C_Email Test Eng INC1287840 Specialist2 4 11/3/2008 22:04
INC1758837 11/4/2008 21:57 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC1294788 Specialist3 4 11/4/2008 22:10
INC1332047 8/5/2008 22:35 11/9/2008 1:22 1-Generic S_Name C_Name C_Email Test Eng INC967140 Specialist4 4 11/5/2008 16:28
INC1763574 11/5/2008 16:18 11/9/2008 1:22 AHRS-G S_Name C_Name C_Email Test Eng INC1298334 Specialist5 4 11/5/2008 22:03
INC1746050 11/3/2008 6:00 11/10/2008 1:32 APS-E S_Name C_Name C_Email Test Ro INC1285084 Specialist6 4 11/6/2008 8:22
INC1771879 11/6/2008 20:41 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305044 Specialist7 4 11/6/2008 21:05
INC1765192 11/5/2008 21:11 11/10/2008 1:32 AHRS-G S_Name C_Name C_Email Test Eng INC1299836 Specialist5 4 11/6/2008 22:30
INC1772344 11/6/2008 21:23 11/10/2008 1:32 1-Generic S_Name C_Name C_Email Test Eng INC1305470 Specialist8 4 11/7/2008 0:00

Today i've updated my query and stage table by deleting all rows where the Audit_created_by was equal to AR_Escalator so that got me half way there. Then i used a query to update the Specialist_Name column with the names from the Audit_created_by column where the initial Specialist_Name column was blank. (all rows in the sample file).

So now the last part that needs to happen is that i need to remove any and all rows where the audit action is listed twice. As in the ticket number INC1765192 case. I wasn't able to use something like this again because of the aggregate function error.

SELECT Top 1 *
FROM WS_Survey_Staging_DB
group by Log_ID
ORDER BY Audit_Status_New, Audit_Created_Date desc

or like this:

SELECT *, count (Log_id)
FROM WS_Survey_Staging_DB
group by Log_ID, Opened_Date, Closed_Date, Product_Code, Specialist_Name, Customer_Name, Customer_Email, Client_Name, Short_Description, Lang_code, Family, Do_Not_Survey, Employee_ID, Enterprise_ID, Call_Center, Customer_Country, Customer_Region, Support_Organization, Assignee_Support_Group, BPO, Site, Customer_Site, Tier1, Tier2, Tier3, Resolution_Category, Entry_ID, Audit_Created_By, Audit_Status_New, Audit_Created_Date
having count (Log_id) > 2
ORDER BY Audit_Status_New, Audit_Created_Date desc

GhantaBro -- can you give an example on your suggestion?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources