Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get frequency of a column in SQL Server

I have a column with values like 1,1,2,1,... and I would want to get the frequency of 1 and 2, I did

SELECT count(column)
FROM table
WHERE column = 1;

SELECT count(column)
FROM table
WHERE column = 2;

But, could I take the frequency with a more direct way?

like image 448
user_012314112 Avatar asked Jul 15 '14 20:07

user_012314112


People also ask

How do you find the number of occurrences in SQL?

COUNT(ID) as NumberOfOccurance:- Counting the number of occurrence of a ID. group by – It is necessary in aggregate function like 'count()' otherwise it will give error. having (COUNT(ID)>1) -Select those rows which count of ID value is greater than 1, that's why it shows '0' and '1'.

What is histogram SQL Server?

Histogram charts are generally used to virtualize histogram data. SQL Server statistics stores the distribution of the column data in a histogram and stores unique values ratio in the density vector. These two meta-data are used by the query optimizer to calculate how many rows will return a query.

How do I find the Properties of a column in SQL?

Right-click a column in the Model Explorer and click Properties. The SQL Server Table Column Editor opens. Select the table from the Table drop-down to define the columns that are available for the table.


2 Answers

Use aggregate functions

 Select column, count(*)
 From   table
 Group By column

This will return one row that contains the count, for each distinct value in column

like image 188
Cheruvian Avatar answered Oct 06 '22 13:10

Cheruvian


One row each value:

select column 'value', count (column) 'Frequency'
from table
group by column

if only 2 values this give you both results in one row

select sum(case when column=1 then 1 else 0 end) as '1 Frequency',
        sum(case when column=2 then 1 else 0 end) as '2 Frequency'
from table
like image 27
Horaciux Avatar answered Oct 06 '22 14:10

Horaciux