CODEWITHSUNDEEP
sqlpowerbidax
MarkJ
MarkJ

Reputation: 45

Summarizing Count of Value

I have a dataset of phone calls that contains a name column. There are repeat names as people can call multiple times. Doing a Count on the column gives us:

What I really want is the breakdown of # of calls, which I'll use as my graph axis:

Thanks for reading!

Upvotes: 0

Views: 73

Answers (4)

Alexis Olson
Alexis Olson

Reputation: 40204

You can do this in DAX along these lines:

CalculatedTable =
VAR Summary = SUMMARIZE(Table1, Table1[Person], "Cnt", COUNT(Table1[Call]))
VAR NumCalls = SELECTCOLUMNS(GENERATESERIES(2,5), "Calls", [Value])
RETURN ADDCOLUMNS(NumCalls, "People", SUMX(Summary, IF([Cnt] = [Calls], 1, 0)))

First, you summarize your data to get a count for each person.

Then you create a table, NumCalls with a single column, Calls, which has values from 2 to 5.

Finally, you add a column to that table which counts the number of people whose Cnt in the Summary table matches the number of Calls in the current table.

Upvotes: 1

Ashish Karnavat
Ashish Karnavat

Reputation: 96

You can try this for counting, check if count is null, and then GROUP BY and ORDER BY :

SELECT numcalls, ISNULL(count(*),0)
FROM t
GROUP BY numcalls
ORDER BY numcalls;

Upvotes: 0

Thomas Z.
Thomas Z.

Reputation: 17

If you do not need 0 values of number of calls, you can work with a subselect in SQL

select numbercalls, count(*)
from (
  select person, count(*) as numbercalls
  from t
  group by person
) group by numbercalls
order by numbercalls;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you want a SQL solution, this is basically aggregation:

select numcalls, count(*)
from t
group by numcalls
order by numcalls;

This will not fill in the 0 values. For that, you can use a table constructor and left join:

select v.numcalls, count(t.numcalls);
from (values (2), (3), (4), (5)) v(numcalls) left join
     t
     on v.numcalls = t.numcalls
group by v.numcalls
order by v.numcalls;

Upvotes: 1

Related Questions