CODEWITHSUNDEEP
mysqlsql
Thilina Akalanka
Thilina Akalanka

Reputation: 163

Select which value is present the most number of times in column in mySQL

Follow is sample table

Project | Reporter | Fixer  | Status    
--------+----------+--------+--------
P1      | Fernando | Janith | closed   
P1      | hasitha  | Nimna  | Fixed    
p1      | Amal     | Nimna  | Fixed    
P2      | Nimal    | Amal   | Fixed    
P3      | Kamal    | Nimal  | Fixed    
P4      | Andrew   | Amal   | Fixed

What I want is Project name, Status count for each project, count of each project's Status 'Fixed', count of project's Status 'Closed' and Fixer who fixed most number of Projects.

except the Bold part I was able to get others using following query

SELECT Project
    ,count(Project) AS ProjectCount
    ,count(CASE STATUS
            WHEN 'Fixed'
                THEN 1
            ELSE NULL
            END) AS Fixed
    ,count(CASE STATUS
            WHEN 'Closed'
                THEN 1
            ELSE NULL
            END) AS Closed
FROM TABLE
GROUP BY Project;

Output I need

Project, ProjectCount, Fixed, Closed, Fixcer    
P1,          3,          2,     1,     Nimna

Upvotes: 0

Views: 66

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The problem here is will select the first fixer with most appear on the project but doesnt handle ties. So you should provide a rule to handle ties.

SQL Fiddle Demo

SELECT Project
    ,count(Project) AS ProjectCount
    ,count(CASE STATUS
            WHEN 'Fixed'
                THEN 1
            ELSE NULL
            END) AS Fixed
    ,count(CASE STATUS
            WHEN 'Closed'
                THEN 1
            ELSE NULL
            END) AS Closed
    , (SELECT fixer
       FROM Table1 B
       WHERE A.`Project` = B.`Project`
       GROUP BY fixer
       ORDER BY COUNT(*) DESC
       LIMIT 1
      ) as Fixer
FROM TABLE1 A
GROUP BY Project;

OUTPUT

| Project | ProjectCount | Fixed | Closed | Fixer |
|---------|--------------|-------|--------|-------|
|      P1 |            3 |     2 |      1 | Nimna |
|      P2 |            1 |     1 |      0 |  Amal |
|      P3 |            1 |     1 |      0 | Nimal |
|      P4 |            1 |     1 |      0 |  Amal |

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

If you want the info only for the project with most times, use this query:

SELECT 
    P1.Project, 
    COUNT(*) ProjectCount, 
    SUM( P1.Status = 'Fixed' ) Fixed, 
    SUM( P1.Status = 'Closed' ) Closed, 
    ( SELECT COUNT(*) cnt
      FROM Project P2
      WHERE P1.Project = P2.Project AND P2.Status = 'Fixed'
      GROUP BY P2.Fixer
      ORDER BY cnt DESC
      LIMIT 1 )
FROM Project P1
GROUP BY P1.Project;

If you want info for all projects, use this one:

SELECT 
    P1.Project, 
    COUNT(*) ProjectCount, 
    SUM( P1.Status = 'Fixed' ) Fixed, 
    SUM( P1.Status = 'Closed' ) Closed, 
    ( SELECT COUNT(*) cnt
      FROM Project P2
      WHERE P1.Project = P2.Project AND P2.Status = 'Fixed'
      GROUP BY P2.Fixer
      ORDER BY cnt DESC
      LIMIT 1 )
FROM Project P1
GROUP BY P1.Project
ORDER BY ProjectCount DESC
LIMIT 1;

Upvotes: 0

Related Questions