Lowest members in column with enum column?-CodePudding

So my (MySQL) Looks like so:

'Table' -> Column : p_id, name, rank, branch(enum (Air Force, Army, Marines, Navy, None)), billet, etc etc

So I am trying to grab the branch with the lowest amount of members and trying to avoid getting anything from (None or Navy 'at this time') The main branches I want to grab stuff from would be Air Force, Army, and Marines. Showing the lowest amount of personnel / members from that branch

So this is what I have done

$stats['lowest'] = DB::run('SELECT MIN(branch) as min_branch FROM personnel')->fetchColumn();

Recruits should go to : <?php echo $stats['lowest']; ?>

It shows Air Force even though it has 3 members in it while Army and Marines haves 0 with the 'branch' title

CodePudding user response：

You are probably looking for a group and count as a sub query

SELECT
    `a`.`branch`
FROM(
    SELECT 
        `personnel`.`branch`,
        COUNT(*) AS `c`    
    FROM 
        `personnel`
    WHERE 
        `personnel`.`branch` IN('Air Force','Army','Marines')
    GROUP BY 
        `personnel`.`branch`
    ORDER BY 
        `c` ASC
) AS `a`
LIMIT 1

BUT

Since you are trying to get Army and Marines which don't have any records... they wont be included in your query. So any result that is returned will never give you a 0 value.

It appears you are trying to count records that dont exist... which wont work without setting up a database view (although someone else might have a better answer)