CODEWITHSUNDEEP
mysqlcountmaxmin
anggasantoso
anggasantoso

Reputation: 23

SELECT MAX and MIN from COUNT - MySQL

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

Upvotes: 1

Views: 11261

Answers (4)

jmail
jmail

Reputation: 6134

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

Upvotes: 0

Vatev
Vatev

Reputation: 7590

(
    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
)

Upvotes: 1

Harish Kanakarajan
Harish Kanakarajan

Reputation: 685

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

Upvotes: -1

Sadikhasan
Sadikhasan

Reputation: 18600

Simple try this

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

Upvotes: 0

Related Questions