Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to count number of occurrences for all different values in database column?

I have a Postgre database that has say 10 columns. The fifth column is called column5. There are 100 rows in the database and possible values of column5 are c5value1, c5value2, c5value3...c5value29, c5value30. I would like to print out a table that shows how many times each value occurs.

So the table would look like this:

Value(of column5)          number of occurrences of the value      c5value1                              1      c5value2                              5      c5value3                              3      c5value4                              9      c5value5                              1      c5value6                              1         .                                  .         .                                  .         .                                  . 

What is the command that does that?

like image 806
Rasto Avatar asked May 19 '10 15:05

Rasto


People also ask

How do I get counts of different values in the same column in SQL?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.

How do you count occurrences of distinct values in SQL?

The following is the syntax to get the count. mysql> SELECT name,COUNT(1) as OccurenceValue FROM DistinctDemo1 GROUP BY name ORDER BY OccurenceValue; Here is the output.

How do you count the occurrence of a value in a column?

You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Excel.

How do I count different values in a column in pandas?

How do you Count the Number of Occurrences in a data frame? To count the number of occurrences in e.g. a column in a dataframe you can use Pandas value_counts() method. For example, if you type df['condition']. value_counts() you will get the frequency of each unique value in the column “condition”.


1 Answers

Group by the column you are interested in and then use count to get the number of rows in each group:

SELECT column5, COUNT(*) FROM table1 GROUP BY column5 
like image 186
Mark Byers Avatar answered Oct 09 '22 03:10

Mark Byers