CODEWITHSUNDEEP
sqlsql-server
Glowie
Glowie

Reputation: 2309

Additional columns in SELECT Count()

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

Upvotes: 0

Views: 646

Answers (7)

Brian Bates
Brian Bates

Reputation: 186

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.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69584

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;

Upvotes: 1

Lamak
Lamak

Reputation: 70678

;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

Upvotes: 3

Adam Miller
Adam Miller

Reputation: 777

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.

Upvotes: 0

mannyyysh
mannyyysh

Reputation: 339

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

Upvotes: 0

Ram
Ram

Reputation: 3091

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;

Upvotes: 0

Tony Stark
Tony Stark

Reputation: 771

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;`

Upvotes: 2

Related Questions