SELECT MAX and MIN from COUNT - MySQL

1
votes

i have employees with officecode table, and i want to show officecode ONLY with max and min employees ,

this is the table

+----------------+------------+
| employeenumber | officecode |
+----------------+------------+
|           1002 | 1          |
|           1056 | 4          |
|           1076 | 1          |
|           1143 | 7          |
|           1165 | 1          |
|           1166 | 6          |
            ....   .
            ....   .
+----------------+------------+

and this is what i want to get:

+------------+----------+
| officecode | empCount |
+------------+----------+
|           1|         6|
|           7|         2|
+------------+----------+

this is my method :

First i COUNT and GROUP them

then SELECT MAX from it and UNION it with SELECT MIN

but the SELECT MIN return wrong officecode

this is my code :

SELECT officecode, MAX(empcount) AS 'empcount'
FROM
    (
    SELECT officecode, count(*) AS 'empCount' 
    FROM employees 
    GROUP BY officecode
    )
AS temp

UNION

    SELECT officecode, MIN(empcount) AS 'empcount'
FROM
    (
    SELECT officecode, count(*) AS 'empCount' 
    FROM employees 
    GROUP BY officecode
    )
AS temp2

the AS tmp must be there unless it will return error

it returned :

+------------+----------+
| officecode | empCount |
+------------+----------+
|           1|         6|
|           1|         2|
+------------+----------+

anyone knwo where i'm wrong? or maybe you have another method because my method look so loooooong, thx

4
mysqlcountmaxmin
why u are doing union your first query should be doing the job select officecode, count(*) AS 'empCount' FROM employees GROUP BY officecode - Abhik Chakraborty
you should try this: SELECT MIN(officecode) AS Minofficecode, MAX(officecode) AS Maxofficecode FROM employees; - jmail
i think you get my question wrong, i mean the employee have his office code, and i want to count the office number of employee, and what i want to get in the end is to show office with most and least number of employee working in there - anggasantoso

4 Answers

1
votes
(
    SELECT officecode, count(*) AS empCount 
    FROM employees 
    GROUP BY officecode 
    ORDER BY empCount ASC 
    LIMIT 1
) 
UNION ALL
(
    SELECT officecode, count(*) AS empCount 
    FROM employees 
    GROUP BY officecode 
    ORDER BY empCount DESC 
    LIMIT 1
)
0
votes

Simple try this

SELECT officecode,MAX(empcount),MIN(empcount) count(*) AS 'empCount' FROM employees GROUP BY officecode
0
votes

you need this:

SELECT *
FROM
((SELECT ACTION, COUNT(ACTION) FROM t1
GROUP BY ACTION
ORDER BY ACTION DESC
LIMIT 1)
UNION 
(SELECT ACTION, COUNT(ACTION) FROM t1
GROUP BY ACTION
ORDER BY ACTION ASC
LIMIT 1))t

check this sqlfiddle: http://sqlfiddle.com/#!2/7116b5/1

-1
votes

Try this,

SELECT officecode, MAX( empCount ) AS  'empCount'
FROM 
    (
    SELECT officecode, COUNT( * ) AS  'empCount'
    FROM employees 
    GROUP BY officecode ORDER BY empCount DESC 
    ) 
AS temp
UNION
SELECT officecode, MIN( empCount ) AS  'empCount'
FROM 
    (
    SELECT officecode, COUNT( * ) AS  'empCount'
    FROM employees 
    GROUP BY officecode ORDER BY empCount ASC 
    )
AS temp2