Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Easy One using counts in wheres

    Some reason I am stuck on this one.

    Select Count(InspectionID) as NumberOfDupes,[Date],Location,PermitID,Inspector,[Type]
    From Inspections
    Where NumberOfDupes > 1 and Deleted <> 1
    Group By [Date],Location,PermitID,Inspector,[Type]
    Order By [Date], NumberOfDupes, Location, PermitID

    and I get...

    Msg 207, Level 16, State 1, Line 3
    Invalid column name 'NumberOfDupes'.

    I need to get a report of all duplicates. If there is only one occurrence then it is not a duplicate.

    Table Structure...

    CREATE TABLE [dbo].[Inspections] (
    [InspectionID] int IDENTITY(1, 1) NOT NULL,
    [Location] int NOT NULL,
    [Date] datetime CONSTRAINT [DF_Inspections_Date] DEFAULT getdate() NOT NULL,
    [Inspector] int NOT NULL,
    [Travel] float CONSTRAINT [DF_Inspections_Travel] DEFAULT 0 NOT NULL,
    [Time] float CONSTRAINT [DF_Inspections_Time] DEFAULT 0 NOT NULL,
    [Type] int CONSTRAINT [DF_Inspections_Type] DEFAULT 1 NOT NULL,
    [Violations] bit CONSTRAINT [DF_Inspections_Green] DEFAULT 0 NOT NULL,
    [CorrectedOnDate] datetime NULL,
    [CorrectedByDate] datetime NULL,
    [NumberOfViolations] int DEFAULT 0 NULL,
    [Mileage] float NULL,
    [ReinspectionDate] datetime NULL,
    [PermitID] int DEFAULT 0 NULL,
    [PermitTypeID] int DEFAULT 0 NULL,
    [Comments] varchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Deleted] bit DEFAULT 0 NULL,
    [Passed] bit DEFAULT 0 NULL,
    [Finaled] bit DEFAULT 0 NULL,
    [LifeSafetyViolations] int DEFAULT 0 NULL,
    [FireProtSysViolations] int DEFAULT 0 NULL,
    [MinorViolations] int DEFAULT 0 NULL,
    [SqrFootage] int DEFAULT 0 NULL,
    [NewNotSynced] bit DEFAULT 0 NULL,
    [AdminViolations] int DEFAULT 0 NULL,
    [ModifiedOn] datetime NULL,
    [ReInspectionFee] float NULL,
    [LifeSafetyCorrectedBy] datetime NULL,
    [FireProtSysCorrectedBy] datetime NULL,
    [MinorCorrectedBy] datetime NULL,
    [AdminCorrectedBy] datetime NULL,
    [LifeSafetyCorrectedOn] datetime NULL,
    [FireProtSysCorrectedOn] datetime NULL,
    [MinorCorrectedOn] datetime NULL,
    [AdminCorrectedOn] datetime NULL,
    [RealMiles] float DEFAULT 0 NULL,
    [RealTime] float DEFAULT 0 NULL,
    [RealTravel] float DEFAULT 0 NULL,
    [AHJID] int DEFAULT 0 NULL,
    [FireProtViolationCorrected] int DEFAULT 0 NULL,
    [HazardsCorrected] int DEFAULT 0 NULL,
    [IsReinspection] bit DEFAULT 0 NULL,
    [InspectionFee] money DEFAULT 0 NULL,
    [ConstructionViolationCorrectedOn] datetime NULL,
    [NumberOfCorrected] int DEFAULT 0 NULL,
    [FireProtViolationOutstanding] int DEFAULT 0 NULL,
    [HazardsOutstanding] int DEFAULT 0 NULL,
    [NumberOfOutstanding] int DEFAULT 0 NULL,
    [AveragedRealNumbers] bit DEFAULT 0 NULL,
    [CTReportId] int NULL,
    CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED ([InspectionID])
    WITH (
    PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    ON [PRIMARY]
    GO

    EXEC sp_addextendedproperty 'MS_Description', N'FID Number', N'schema', N'dbo', N'table', N'Inspections', N'column', N'Location'
    GO

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [dbo].[Inspections_triu] ON [dbo].[Inspections]
    WITH EXECUTE AS CALLER
    FOR INSERT, UPDATE
    AS
    BEGIN
    /* Trigger body */
    IF @@ROWCOUNT=0 RETURN
    UPDATE U SET ModifiedOn = GETDATE()
    FROM INSPECTIONS U INNER JOIN INSERTED I ON U.InspectionId = I.InspectionId

    END
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you create a derived column (by naming an expression in the SELECT list), that derived column doesn't actually exist until the result set is being built. Check the sequence of clauses listed at SELECT (Transact-SQL) for more details on what happens when while executing a SELECT statement.

    You should use:
    Code:
    Select Count(InspectionID) as NumberOfDupes
    ,  [Date],Location,PermitID,Inspector,[Type] 
       From Inspections
       Where Count(InspectionID) > 1 and Deleted <> 1
       Group By [Date],Location,PermitID,Inspector,[Type]
       Order By [Date], Count(InspectionID), Location, PermitID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Error

    Ok, thanks but now I am getting this error...

    Msg 147, Level 15, State 1, Line 1
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  4. #4
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Got it.

    I got it...

    Select Count(InspectionID),[Date],Location,PermitID,Inspector,[Type]
    From Inspections
    Where Deleted <> 1
    Group By [Date],Location,PermitID,Inspector,[Type]
    Having Count(InspectionID) > 1
    Order By Count(InspectionID),[Date], Location, PermitID


    http://technet.microsoft.com/en-us/l.../ms180199.aspx

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good catch! I'm obliviously dain bramaged today.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •