Ranking query problem

[gmc1103]

Hi

 

I'm having a problem regarding the following query

 

Error Code: 1242

Subquery returns more than 1 row

 

SET @Rank := 0;
SELECT @curRank := @curRank + 1 AS rank,  u.nome, (SELECT COUNT(r.id_utilizador) FROM rel_atividades_utilizador)AS total FROM rel_atividades_utilizador AS r 
INNER JOIN utilizador AS u ON r.id_utilizador = u.id_utilizador, (SELECT @curRank := 0) t ORDER BY (total) DESC 

I have a table who keeps the id_utilizador (user_id) each time this user save new info into this table

 

I want to know the ranking of each users

 

For instance user x as 43 posts, so my query should return

 

rank       name      total

1             X            43

2             Y            40

 

etc

 

Any help with this query?

 

Thanks

 

 

[Jacques1]

Your count part makes no sense to me.

 

Write a query which simply counts the activities per user. Then add the ranks.

SELECT
    @rank := @rank + 1 AS rank,
    nome,
    cnt
FROM (
    SELECT
        u.nome,
        COUNT(*) AS cnt
    FROM
        rel_atividades_utilizador AS r
        JOIN utilizador AS u ON r.id_utilizador = u.id_utilizador
        CROSS JOIN (SELECT @rank := 0) AS rank
    GROUP BY
        u.nome
    ORDER BY
        cnt DESC
) AS totals
;

[gmc1103]

Hi Jacques

How are you?

Thank you for your answer, it works.

I have all the names and the activities per user and i want to know how many have been evaluated.

This information is kept in another table (yes, is no need, i need to rebuild this)

CREATE TABLE `rel_atividades_avaliacao` (
  `id_avaliacao` int(11) NOT NULL AUTO_INCREMENT,
  `id_atividades` int(11) NOT NULL,
  `avaliado` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT '-1',
  `data_prevista` varchar(50) COLLATE utf8_bin NOT NULL,
  `motivo` varchar(300) COLLATE utf8_bin DEFAULT '-',
  `adequacaos` int(11) DEFAULT NULL,
  `participacaos` int(11) DEFAULT NULL,
  `consecucaos` int(11) DEFAULT NULL,
  `disps` int(11) DEFAULT NULL,
  `mats` int(11) DEFAULT NULL,
  `balanco` varchar(300) COLLATE utf8_bin DEFAULT NULL,
  `ficheiro` varchar(300) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id_avaliacao`),
  KEY `id_atividades` (`id_atividades`),
  CONSTRAINT `rel_atividades_avaliacao_ibfk_1` FOREIGN KEY (`id_atividades`) REFERENCES `atividades` (`id_atividades`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=563 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

You can see that "id_atividades" is the foreign key in this table

Image from this table

 

Check the image in (https://ibb.co/hJQUdF)

 

So i need to know from "avaliado" how many from each user is different from -1 (not evaluated)

 

 

Is possible or i need to change everything now?

 

Thanks

[Jacques1]

Definitely repair your tables. This is quite a mess.

 

You need to start using appropriate data types and stop putting all kinds of values into VARCHARs. A VARCHAR accepts any nonsense and doesn't provide any type safety, so your database may already be full of garbage. Cryptic values like "-1" and "Sim" make things even worse.

 

Use meaningful types and values. Dates belong into date colums, booleans into boolean columns, numberr into numeric columns. When you have a complex status field with multiple possible values, either use an ENUM or a foreign key pointing to a separate value table. MySQL should be able to validate the incoming data and reject obvious nonsense.

 

You should also seriously consider switching to English identifiers. The language may not have been an issue as long as you were the only one working on the code. But now you're on the Internet asking for help, and not everybody happens to speak your language.

[gmc1103]

Hi Jacques

Again thank you for your help.

It is really good idea what you are suggesting. I had 2 tables but then they ask me to have evaluation table and to don't make any disaster on those tables i have created another one...that's why i have 3.

Let me explain how does it works

A  teacher should be able to shedule an activity.

The activity has from other tables (project, user, year)

and she must have an evaluation with some unique fields

So i made this new database, can you give me your opinion please? All the fields in english

Image here 

https://ibb.co/dN07Fv

Thank you