Getting rows with less value for a specific column in SQL

Jon Goe Source

With the follow SQL query,

SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID) 
    FROM Problem 
    WHERE Problem.Solver_Assigned_ID IN (
        SELECT Specialist_Category.Specialist_ID 
        FROM Specialist_Category, Specialist_Category_Name
        WHERE Specialist_Category.Category_ID  = Specialist_Category_Name.Specialist_Category_ID 
        AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
    GROUP BY Problem.Solver_Assigned_ID

I am getting the table

+-----------------+-----------------------------------+
| Solver assigned | COUNT(Problem.Solver_Assigned_ID) |
+-----------------+-----------------------------------+
| 12              | 2                                 |
+-----------------+-----------------------------------+
| 17              | 3                                 |
+-----------------+-----------------------------------+
| 20              | 2                                 |
+-----------------+-----------------------------------+

I would like to get the table with the minimum COUNT(Problem.Solver_Assigned_ID) value.

I have tried

SELECT (Problem.Solver_Assigned_ID, MIN(COUNT(Problem.Solver_Assigned_ID)))
FROM (
    SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID) 
        FROM Problem 
        WHERE Problem.Solver_Assigned_ID IN (
            SELECT Specialist_Category.Specialist_ID 
            FROM Specialist_Category, Specialist_Category_Name
            WHERE Specialist_Category.Category_ID  = Specialist_Category_Name.Specialist_Category_ID 
            AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
        GROUP BY Problem.Solver_Assigned_ID
)

The expected result is

+-----------------+-----------------------------------+
| Solver assigned | COUNT(Problem.Solver_Assigned_ID) |
+-----------------+-----------------------------------+
| 12              | 2                                 |
+-----------------+-----------------------------------+
| 20              | 2                                 |
+-----------------+-----------------------------------+

But to no avail. Any help would be appreciated. Thanks

mysqlsql

Answers

answered 6 months ago scaisEdge #1

You could use having for filter the result based on the min of the subselect

SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID) 
FROM Problem 
WHERE Problem.Solver_Assigned_ID IN (
    SELECT Specialist_Category.Specialist_ID 
    FROM Specialist_Category, Specialist_Category_Name
    WHERE Specialist_Category.Category_ID  = Specialist_Category_Name.Specialist_Category_ID 
    AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
GROUP BY Problem.Solver_Assigned_ID
having COUNT(Problem.Solver_Assigned_ID)  = (select min(my_count ) 
      from (
      SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID)  as my_count
          FROM Problem 
          WHERE Problem.Solver_Assigned_ID IN (
              SELECT Specialist_Category.Specialist_ID 
              FROM Specialist_Category, Specialist_Category_Name
              WHERE Specialist_Category.Category_ID  = Specialist_Category_Name.Specialist_Category_ID 
              AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
          GROUP BY Problem.Solver_Assigned_ID )  t1)

answered 6 months ago cdaiga #2

You have to do an inner join with your the two queries you have tried as shown below:

SELECT A.* FROM
(SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID) 
FROM Problem 
WHERE Problem.Solver_Assigned_ID IN (
    SELECT Specialist_Category.Specialist_ID 
    FROM Specialist_Category, Specialist_Category_Name
    WHERE Specialist_Category.Category_ID  = Specialist_Category_Name.Specialist_Category_ID 
    AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
GROUP BY Problem.Solver_Assigned_ID) A
INNER JOIN 
(SELECT (C.Solver_Assigned_ID, MIN(C.countValue))
 FROM (
 SELECT Problem.Solver_Assigned_ID, COUNT(Problem.Solver_Assigned_ID) countValue
    FROM Problem 
    WHERE Problem.Solver_Assigned_ID IN (
        SELECT Specialist_Category.Specialist_ID 
        FROM Specialist_Category, Specialist_Category_Name
        WHERE Specialist_Category.Category_ID  =    
        Specialist_Category_Name.Specialist_Category_ID 
        AND Specialist_Category_Name.Specialist_Category_Name = "Mouse") 
        GROUP BY Problem.Solver_Assigned_ID
) C GROUP BY C.Solver_Assigned_ID) B
ON A.Solver_Assigned_ID=B.Solver_Assigned_ID;

comments powered by Disqus