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?

Ok, reading your question carefully I understand that you want to count ;) — davidbuzatto
– davidbuzatto, Commented Mar 16, 2013 at 14:06
but i need to get the max in the count column. how do i get that? — LynAs
– LynAs, Commented Mar 16, 2013 at 14:22

mellamokb · Accepted Answer · 2013-03-16 13:59:29Z

5

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

answered Mar 16, 2013 at 13:59

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LynAs Over a year ago

i also need to get the max in the count column. how do i get that?

mellamokb Over a year ago

You can sort by count(emNo), and then take the first row, e.g., .... ORDER BY count(emNo) DESC LIMIT 1: sqlfiddle.com/#!2/4f5df/17

LynAs Over a year ago

.... ORDER BY count(emNo) DESC this gives me result but when i add .... ORDER BY count(emNo) DESC limit 1 i get error (command not properly ended)

Collectives™ on Stack Overflow

How to do sql loop

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related