问题描述:

This SQL Server query works

SELECT

dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs

FROM

dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.SEM_CLIENT, dbo.SEM_AGENT, dbo.IDENTITY_MAP

WHERE

sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_CLIENT.COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID

and dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID

and dbo.SEM_AGENT.TIME_STAMP > DATEDIFF(second, '19700101', DATEADD(day, -1, GETDATE())) * CAST(1000 as bigint)

GROUP BY dbo.sem_computer.COMPUTER_ID

HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1

ORDER BY Duplicate_Hardware_IDs DESC;

But I want to SELECT additional columns (to show which computers have the duplicate COMPUTER_ID)

SELECT

dbo.sem_computer.COMPUTER_NAME

, [IP_ADDR1_TEXT]

, dbo.SEM_AGENT.AGENT_VERSION

, dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs

FROM

dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.SEM_CLIENT, dbo.SEM_AGENT, dbo.IDENTITY_MAP

WHERE

sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_CLIENT.COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID

and dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID

and dbo.SEM_AGENT.TIME_STAMP > DATEDIFF(second, '19700101', DATEADD(day, -1, GETDATE())) * CAST(1000 as bigint)

GROUP BY dbo.sem_computer.COMPUTER_ID

HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1

ORDER BY Duplicate_Hardware_IDs DESC;

I get error

Column 'dbo.sem_computer.COMPUTER_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How to fix?

UPDATE: when I execute

SELECT

dbo.sem_computer.COMPUTER_NAME

, [IP_ADDR1_TEXT]

, dbo.SEM_AGENT.AGENT_VERSION

, dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs

FROM

dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.SEM_CLIENT, dbo.SEM_AGENT, dbo.IDENTITY_MAP

WHERE

sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_CLIENT.COMPUTER_ID

and sem_computer.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID

and dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID

and dbo.SEM_AGENT.TIME_STAMP > DATEDIFF(second, '19700101', DATEADD(day, -1, GETDATE())) * CAST(1000 as bigint)

GROUP BY dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT], dbo.SEM_AGENT.AGENT_VERSION, dbo.sem_computer.COMPUTER_ID

HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1

ORDER BY Duplicate_Hardware_IDs DESC;

it results in

COMPUTER_NAME IP_ADDR1_TEXT AGENT_VERSION COMPUTER_ID Duplicate_Hardware_IDs

ABC 10.10.10.10 12.1 56604FEE0AF 3

But I am looking for

COMPUTER_NAME IP_ADDR1_TEXT AGENT_VERSION COMPUTER_ID Duplicate_Hardware_IDs

ABC 10.10.10.10 12.1 56604FEE0AF 3

123 10.10.10.15 12.2 56604FEE0AF 3

XYZ 10.10.10.25 12.2 56604FEE0AF 3

UPDATE 2:

I included FROM and WHERE

网友答案:
;WITH CTE AS
(
    SELECT  dbo.sem_computer.COMPUTER_NAME,
            [IP_ADDR1_TEXT],
            dbo.SEM_AGENT.AGENT_VERSION,
            dbo.sem_computer.COMPUTER_ID, 
            N = COUNT(*) OVER(PARTITION BY dbo.sem_computer.COMPUTER_ID)
    FROM
    ...
    WHERE 
    ...
)
SELECT *
FROM CTE
WHERE N > 1
ORDER BY N DESC
网友答案:

Just include the columns in your Select list to Group By list

SELECT 
    dbo.sem_computer.COMPUTER_NAME
,   [IP_ADDR1_TEXT]
,   dbo.SEM_AGENT.AGENT_VERSION
,   dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs
FROM
...
WHERE 
...
GROUP BY 
     dbo.sem_computer.COMPUTER_ID, 
     dbo.sem_computer.COMPUTER_NAME,
     ...
HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1
ORDER BY Duplicate_Hardware_IDs DESC;`
网友答案:

Any column that is in select statement but not in any aggregate function (MIN,MAX,SUM,COUNT,AVG), should come in GROUP BY clause.

   SELECT 
         dbo.sem_computer.COMPUTER_NAME
        ,[IP_ADDR1_TEXT]
        ,dbo.SEM_AGENT.AGENT_VERSION
        ,dbo.sem_computer.COMPUTER_ID
        ,COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs
    FROM
    ...
    WHERE 
    ...
    GROUP BY dbo.sem_computer.COMPUTER_NAME
            ,[IP_ADDR1_TEXT]
            ,dbo.SEM_AGENT.AGENT_VERSION
            ,dbo.sem_computer.COMPUTER_ID
    HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1
    ORDER BY Duplicate_Hardware_IDs DESC;
网友答案:

You can add the columns in the GROUP BY clause as others mentioned or if you don't want to do a group by with thee columns then try the following

SELECT 
    dbo.sem_computer.COMPUTER_NAME,
    [IP_ADDR1_TEXT],
    dbo.SEM_AGENT.AGENT_VERSION,
    dbo.sem_computer.COMPUTER_ID, Duplicate_Hardware_IDs
FROM dbo.sem_computer c
INNER JOIN
dbo.SEM_AGENT ON //add the join conditions 
INNER JOIN 
(SELECT 
    dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs
FROM
 ...
WHERE 
...
GROUP BY dbo.sem_computer.COMPUTER_ID
HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1) temp
ON c.COMPUTER_ID=temp.COMPUTER_ID
ORDER BY Duplicate_Hardware_IDs DESC;
网友答案:

You have to add all non aggregate fields in group by clause: GROUP BY dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT] ,dbo.SEM_AGENT.AGENT_VERSION, dbo.sem_computer.COMPUTER_ID

SELECt dbo.sem_computer.COMPUTER_NAME,IP_ADDR1_TEXT,dbo.SEM_AGENT.AGENT_VERSION,dbo.sem_computer.COMPUTER_ID, COUNT(dbo.sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs FROM ... WHERE ...GROUP BY dbo.sem_computer.COMPUTER_NAME,IP_ADDR1_TEXT,dbo.SEM_AGENT.AGENT_VERSION, dbo.sem_computer.COMPUTER_ID HAVING COUNT(dbo.sem_computer.COMPUTER_ID) > 1ORDER BY Duplicate_Hardware_IDs DESc
网友答案:

It's hard to tell from your query, as you don't show how you're relating SEM_AGENT to the query.

One option is to add the field to the group by--just add a comma and the field name.
You could also use an aggregate funtion like LAST() where you're returning the field in the query. You might also be able to use a sub-select to return the field. I'm not sure about that one, though.

网友答案:

You can solve this problem by JOINing your two queries. Something like the following should work.

;WITH Duplicates AS (
    SELECT 
        sem_computer.COMPUTER_ID, 
        COUNT(sem_computer.COMPUTER_ID) as Duplicate_Hardware_IDs
    FROM
        sem_computer
    WHERE 
        ...
    GROUP BY sem_computer.COMPUTER_ID
)
SELECT 
    sem_computer.COMPUTER_NAME,
    sem_computer.[IP_ADDR1_TEXT],
    SEM_AGENT.AGENT_VERSION,
    sem_computer.COMPUTER_ID
FROM
        sem_computer
    INNER JOIN
        Duplicates
            ON sem_computer.COMPUTER_ID = Duplicates.COMPUTER_ID
    INNER JOIN
        SEM_AGENT
            ON SEM_AGENT.COMPUTER_ID = sem_computer.COMPUTER_ID
WHERE 
    Duplicates.Duplicate_Hardware_IDs > 1
ORDER BY Duplicate_Hardware_IDs DESC;

Find the duplicates first and then JOIN on those and return the additional information. You will also have to JOIN to the SEM_AGENT table.

相关阅读:
Top