CODEWITHSUNDEEP
mysqlsqldatabase
Mahab Phoenix Khan
Mahab Phoenix Khan

Reputation: 119

How to format and print a result of sql query

I recently got this question on interview which I failed to answer. The question was to list the number of duplicates that appear in a column employer like from this table

id  |   employer   |  employee
1   |    isro      |      dude1
2   |    isro      | dude 2
3   |    cnd       | dude 3
4   |   df         | dsfdef
5   | dfdf         | dfdfd
...

so the result should be like

isro  = 2
df    = 4
dfsf  = 6

how do i achieve this? I know there is count(*) which i could use with select statement with where clause, but how do i achieve the above result.

Upvotes: 0

Views: 157

Answers (2)

ekill
ekill

Reputation: 176

assuming TableName is the name of the table you want to select from, this would be your answer.

SELECT employer, count(employer) 
  FROM TableName 
  GROUP BY employer
  HAVING COUNT(*) > 1

here is an answer to a very similar question that has some more info for you.

How to count occurrences of a column value efficiently in SQL?

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

The HAVING clause can be used to filter on aggregated values:

SELECT employer, COUNT(*)
FROM yourTable
GROUP BY employer
HAVING COUNT(*) > 1

Upvotes: 2

Related Questions