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

0
votes

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
2
mysqlsql
What problem are you running into? What about your current code doesn't work? – dfundako
why you output doesnt have p2, p3, and p4? – Juan Carlos Oropeza
I assume you want something like SELECT COUNT(1) FROM tbl WHERE STATUS = 'Fixed' GROUP BY Fixer. – Razvan Manolescu
Do you want a row of output for each project or just one most row? – Dylan Su

2 Answers

1
votes

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 |
0
votes

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;