Count by unique values in other fields in Oracle SQL

Question

I have table like this:

Year    Month   Type
2013    4   31
2013    3   31
2014    5   40
2014    6   41
2015    5   31
2015    7   40
2013    4   31
2013    3   31
2014    5   40
2014    6   41
2015    5   31
2015    7   40
2013    4   31
2013    3   31

I would like to count number of appearance of each combination. So output should be something like this:

Year    Month   Type    Count_of_appearance
2013    3   31  3
2013    4   31  3
etc..

I've been using something like:

SELECT Year,
       Month,
       Type,
       (SELECT COUNT (*)
          FROM myTable
         WHERE (...im lost in defining a condition in order to make this work...)
           AS Count_of_appearance
  FROM employee;

I'm lost at defining functional WHERE clause.

Problem is I a have a lot of fields like this and a lot of unique values. Table is 8Gb big.

Gordon Linoff · Accepted Answer · 2019-06-05 11:32:36Z

1

You are looking for GROUP BY:

SELECT Year, Month, Type, COUNT(*) AS Count_of_appearance
FROM employee
GROUP BY Year, Month, Type
ORDER BY Year, Month, Type;

To ensure that the results are in the right order, you should include an ORDER BY as well.

answered Jun 5, 2019 at 11:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Count by unique values in other fields in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related