Count the Occurrences of Distinct Values

Count the occurrences of DISTINCT values

SELECT name,COUNT(*) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

Count occurrences of distinct values

Scince you are grouping by a_id and year you of course get only 1 distinct value for group. Simply change count(distinct a_id) to count(*).

For example you get group:

1        2012
1        2012

Notice in this group distinct a_id values is 1. But you want count of all rows in group. With distinct you will get 1 as occurence in all groups.

EDIT:

Ok, I have missed that you are grouping only by year, so you should group by a_id also. The rest of the answer stays as is. So you end up with:

SELECT a__id, year, count(*) as occurrences
FROM table1
GROUP by a__id, year

MySql: Count occurrences of distinct values by date

You are missing group by and aggregation functions:

SELECT CONCAT(MONTH(`Date) , YEAR(`Date`)) AS MYYYY , 
       COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY CONCAT(MONTH(`Date`), YEAR(`Date`) )

As a note. I think it is better to put year-month combinations in the YYYYMM format. This makes it nicer to sort the data. You can write this as:

SELECT DATE_FORMAT(`Date`, '%Y-%m') as YYYYMM, COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY YYYYMM
ORDER BY YYYYMM;

In fact, you could just leave it as two columns:

SELECT YEAR(`Date`) as YYYY, MONTH(`Date`) as MM,
       COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY YYYY, MM
ORDER BY YYYY, MM;

How do I count occurrences of distinct data in two columns from 1 table MySql

SELECT name, COUNT(*)
FROM ( SELECT firstname name
       FROM meeting 
       UNION ALL
       SELECT secondname
       FROM meeting ) x
GROUP BY name;

Counting the occurrences of distinct values in a column

This particular case

You need a plpgsql function:

create or replace function my_arr_to_jsonb(text[])
returns jsonb language plpgsql as $$
declare
    agg int[] = array[0, 0, 0];
    s text;
begin
    foreach s in array $1 loop
        if lower(s) = 'high' then
            agg[1]:= agg[1]+ 1;
        elsif lower(s) = 'medium' then
            agg[2]:= agg[2]+ 1;
        else
            agg[3]:= agg[3]+ 1;
        end if;
    end loop;
    return jsonb_build_object(
        'high', agg[1],
        'medium', agg[2],
        'low', agg[3]);
end $$;

The function in action:

with my_table (id, score_labels) as (
values
(1, '{"total": "High", "risk": "High"}'::jsonb),
(2, '{"total": "High", "risk": "Low"}'::jsonb),
(3, '{"total": "Low", "risk": "Medium"}'::jsonb)
)

select 
    my_arr_to_jsonb(array_agg(score_labels->>'total')) as total, 
    my_arr_to_jsonb(array_agg(score_labels->>'risk')) as risk
from my_table

               total                |                risk                
------------------------------------+------------------------------------
 {"low": 1, "high": 2, "medium": 0} | {"low": 1, "high": 1, "medium": 1}
(1 row)

It is possible to use the algorithm in the function to create a custom aggregate function (see below).

Generalized solution

The question touched upon the interesting topic of counting the occurrences of distinct values in a table column using a single aggregation function.

create or replace function count_labels_state(text[], text)
returns text[] language plpgsql as $$
declare
    i int;
begin
    if $2 is not null then
        i:= array_position($1, quote_ident($2));
        if i is null then
            $1:= $1 || array[quote_ident($2), '0'];
            i:= cardinality($1)- 1;
        end if;
        $1[i+1]:= $1[i+1]::int+ 1;
    end if;
    return $1;
end $$;

create or replace function count_labels_final(text[])
returns jsonb language plpgsql as $$
declare
    j jsonb = '{}';
    i int = 1;
begin
    loop exit when i > cardinality($1); 
        j:= j || jsonb_build_object(trim($1[i], '"'), $1[i+1]::int);
        i:= i+ 2;
    end loop;
    return j;
end $$;

create aggregate count_labels(text) (
    sfunc = count_labels_state,
    stype = text[],
    finalfunc = count_labels_final
);

Usage. Instead of:

with my_table (label) as (
values
    ('low'), ('medium'), ('high'), ('low'),
    ('low'), ('medium'), ('high'), ('low'),
    ('low'), ('unknown')
)

select
    count(*) filter (where label = 'low') as low,
    count(*) filter (where label = 'medium') as medium,
    count(*) filter (where label = 'high') as high,
    count(*) filter (where label = 'unknown') as unknown
from my_table;

 low | medium | high | unknown 
-----+--------+------+---------
   5 |      2 |    2 |       1
(1 row)

you can use this (you do not have to know labels):

select count_labels(label) as labels
from my_table;

                      labels                      
--------------------------------------------------
 {"low": 5, "high": 2, "medium": 2, "unknown": 1}
(1 row)

The aggregate works well on integer columns:

with my_table (n) as (
values 
    (1), (2), (3), (4),
    (1), (2), (1), (2)
)

select count_labels(n::text) as integers
from my_table;

              integers              
----------------------------------
 {"1": 3, "2": 3, "3": 1, "4": 1}
(1 row) 

In case of other types one should remember that the aggregate works on text representations of values (e.g. numeric 1.10 = 1.1 but '1.10' <> '1.1').

Count number of occurrences for each unique value

Yes, you can use GROUP BY:

SELECT time,
       activities,
       COUNT(*)
FROM table
GROUP BY time, activities;

Select distinct values, and count number of occurrences

You are using an aggregation function (COUNT) without a GROUP BY clause, then mysql doesn't know how you want to aggregate your data:

SELECT 
  category,
  count(category) as category_count
FROM classes
GROUP BY category
ORDER BY category ASC

How to count number of occurrences of distinct values from an array of objects in Javascript?

You'll need to know to which name a count belongs, so I propose not to output an array that gives you no clue about that, but an object keyed by names and with as value the corresponding count:

var result = array.reduce( (acc, o) => (acc[o.name] = (acc[o.name] || 0)+1, acc), {} );

var array =    [        {"name":"abc","age":20},        {"name":"abc","age":20},        {"name":"abc","age":20},        {"name":"xyz","age":21},        {"name":"xyz","age":21}    ];    var result = array.reduce( (acc, o) => (acc[o.name] = (acc[o.name] || 0)+1, acc), {} );
console.log(result);