How to do sql loop

Question

Table works(emNo, comNo, salary).

I can get distinct comNo using "select distinct comNo from works". Suppose it gives me a column with 5 rows. How do I count "emNo`" for each of those rows?

Answer 1

You can use GROUP BY to aggregate per type of comNo.

SELECT
  comNo,
  count(emNo)
FROM
  works
GROUP BY
  comNo

This will return one row per distinct value of comNo along with the count of records per group.

Demo: http://www.sqlfiddle.com/#!2/4f5df/1