Question 20648 in Mysql Question posted in
The official MySQL documentation can be found here.

Select and count all entries and group them according to the entries – Mysql

I have the following table and i am supposed to count the entries(5,4,3,2,1) per column name

ID ClientID Responsiveness1 Responsiveness2 Reliability1
1024 3511 5 4 3
1025 4571 5 3 5
1026 3827 4 5 4
1027 7652 1 1 1
1028 7778 2 2 2
1029 7612 1 1 2

I tried counting it manually (Select Count(Responsiveness1) from table where Responsiveness1 = ‘5’) by column name and by the range (5,4,3,2,1) and got the output. Unfortunately though, I have to count it per column and per range then display it on my table.

Is there an easier and more efficient way to do it to have an output of:

Rate Responsiveness1 Responsiveness2 Reliability1
5 2 1 1
4 1 1 1
3 0 1 1
2 1 1 2
1 2 2 1

Instead of having to count it by column name and then inserting it in the output table?

Tags:

2

Answers


  1. 0

    You can join your table with a table of rates and count entries conditionally

    with rates(n) as (
  values row(1),row(2),row(3),row(4),row(5)
)
select r.n rate,
   sum(Responsiveness1 = r.n) Responsiveness1,
   sum(Responsiveness2 = r.n) Responsiveness2,
   sum(Reliability1 = r.n) Reliability1
from rates r
left join mytable on Responsiveness1 = r.n or Responsiveness2 = r.n or Reliability1  = r.n 
group by r.n
order by r.n desc

    db<>fiddle

    Login or Signup to reply.
  2. 0

    You may use full join emulation for MySql thru union all and then "pivot" table. This case is usable for an indefinite set of rate values.

    create table Rates (ID int,ClientID int,Responsiveness1 int,Responsiveness2 int,Reliability1 int);
insert into Rates values(1024, 3511, 5, 4, 3);
insert into Rates values(1025, 4571, 5, 3, 5);
insert into Rates values(1026, 3827, 4, 5, 4);
insert into Rates values(1027, 7652, 1, 1, 1);
insert into Rates values(1028, 7778, 2, 2, 2);
insert into Rates values(1029, 7612, 1, 1, 2);

select n rate
      ,max(case when code="Responsiveness1" then qty else 0 end)Responsiveness1
      ,max(case when code="Responsiveness2" then qty else 0 end)Responsiveness2
      ,max(case when code="Reliability1" then qty else 0 end)Reliability1
from ( 
  select "Responsiveness1" code,Responsiveness1 n,count(*) qty
      from Rates r group by Responsiveness1
  union all
  select "Responsiveness2" code,Responsiveness2 n,count(*) qty
      from Rates r group by Responsiveness2
  union all
  select "Reliability1" code,Reliability1 n,count(*) qty
      from Rates r group by Reliability1
  )u
group by n
order by n desc
    rate Responsiveness1 Responsiveness2 Reliability1
    5 2 1 1
    4 1 1 1
    3 0 1 1
    2 1 1 2
    1 2 2 1

    I’ll add Pivot result example

    select code
      ,max(case when n=1 then qty else 0 end) rate1
      ,max(case when n=2 then qty else 0 end) rate2
      ,max(case when n=3 then qty else 0 end) rate3
      ,max(case when n=4 then qty else 0 end) rate4
      ,max(case when n=5 then qty else 0 end) rate5
from ( 
  select "Responsiveness1" as code,Responsiveness1 n,count(*) qty
      from Rates r group by Responsiveness1
  union all
  select "Responsiveness2" as code,Responsiveness2 n,count(*) qty
      from Rates r group by Responsiveness2
  union all
  select "Reliability1" as code,Reliability1 n,count(*) qty
      from Rates r group by Reliability1
  )u
group by code

    Table

    code rate1 rate2 rate3 rate4 rate5
    Responsiveness1 2 1 0 1 2
    Responsiveness2 2 1 1 1 1
    Reliability1 1 2 1 1 1

    What case is suitable in a specific case – customer request or developer preferences:)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search