Can't use SUM in SQL when the value is not integer-CodePudding

I am using below SQL and couldn't able to get the count of the instance. The end goal that i am trying to achieve is that i want to know that count of teams and instances that have the same the same Issue.

My SQL query:

SELECT 
    priority,
    QID,
    issue,
    solution,
    team,
    instance,
    COUNT(team) AS TEAM_COUNT,
    SUM(instance) AS Instance_count
FROM  
    ABCDTABLE
WHERE 
    priority != 'NONE'
GROUP_BY
    priority, QID, issue, solution, team, instance
ORDER_BY
    priority, QID, issue, solution, team, instance

I get this error:

Numeric value 'i-21232' is not recognized

Sample data:

Priority    QID           Issue                SOLUTION       TEAM    INSTANCE
HIGH        123   Wrong package version     Update Package.   Devops.  i-21232
HIGH        123   wrong package version     update package.   infra.   i-12341
MEDIUM      721.  SSH vulnerability.      Remove old version.  App     i-323232

Expected Output:

Priority  QID.           Issue              Solution      count(TEAM) COUNT(INSTANCE)
HIGH.     123.   Wrong Package Version.   Update package.       2                2

Thank You

CodePudding user response：

This query will give the desired result

SELECT 
    priority,
    QID,
    issue,
    solution,
    COUNT(team) AS TEAM_COUNT,
    COUNT(instance) AS Instance_count
FROM  
    ABCDTABLE
WHERE 
    priority != 'NONE'
GROUP_BY
    priority, QID, issue, solution
ORDER_BY
    priority, QID, issue, solution

CodePudding user response：

You may need to remove the TEAM and INSTANCE columns from the GROUP BY, and change the SUM(INSTANCE) to a COUNT(INSTANCE).

As stated above, you can't SUM data the contains non-numerical values.

E.g. does the below work for you?

SELECT 
    priority,
    QID,
    issue,
    solution,
    team,
    instance,
    COUNT(team) AS TEAM_COUNT,
    COUNT(instance) AS Instance_count
FROM  
    ABCDTABLE
WHERE 
    priority != 'NONE'
GROUP_BY
    priority, QID, issue, solution
ORDER_BY
    priority, QID, issue, solution, team, instance