CODEWITHSUNDEEP
mysqlsql
mrajesh016
mrajesh016

Reputation: 17

My sql listing entries which are repeated more than certain value

I have a staff table like this --->

+------+------------------+------+------------+--------+
| EC   | Name             | Code | Dob        | Salary |  
+------+------------------+------+------------+--------+
| 2001 | ROBBIE KEANE     | VSS1 | 1990-05-16 |  18000 |
| 2002 | ANSUMAN BANERJEE | VSS1 | 1985-05-21 |  18000 |
| 2003 | OMAR GONZALEZ    | SACP | 1989-04-16 |  20000 |
| 2004 | ALAN GORDON      | IALO | 1989-05-03 |  20000 |
| 2005 | ROBBIE KEANE     | IALO | 1988-01-16 |  18000 |
| 2006 | CHANDLER HOFFMAN | BBDP | 1988-07-17 |  22000 |
| 2007 | PAUL POGBA       | BHSM | 1990-08-16 |  18000 |
| 2008 | SHINJI KAGAWA    | LPDC | 1991-01-20 |  18000 |
+------+------------------+------+------------+--------+

And now i want to list those codes (like VSS1), which have less than specified number of people assigned with them(say like less than 2) , how can i do this please help.

My query up till now is-->

SELECT Code,count(*) as 'Number of Staff' from STAFF where Code IN (SELECT Code from STAFF GROUP BY CODE LIMIT 2);

But this is not working.

Upvotes: 0

Views: 31

Answers (2)

potashin
potashin

Reputation: 44581

You can filter row count for each Code group with the HAVING clause :

SELECT Code
     , COUNT(*)
FROM STAFF 
GROUP BY Code
HAVING COUNT(*) < 2

Upvotes: 1

xQbert
xQbert

Reputation: 35323

If you need to know the names of the people having this count less than 2 then...

SELECT S.EC, S.Name, S.Code, S.DOB, S.Salary, SC.Code, SC.Cnt
FROM STAFF S
INNER JOIN (SELECT Count(*) cnt, Code FROM STAFF GROUP BY CODE) SC
 on S.Code = SC.code
WHERE SC.CNT < 2

should work in SQL server and mySQL. Though SQL Sever could also use a windowed set which would be faster.

If however, you just need to know the Codes having less than a certain number, notulysses having clause should fit the bill.

Upvotes: 1

Related Questions