Sum of Count’s from different columns

Asked

Viewed 41 times

0

Good morning, I have a school activity in which I aim to develop the consultations sql’s for each question. The activity has 1000 megasena results, containing the columns sorteio, data_sorteio, dezena1, dezena2, dezena3, dezena4, dezena5 and dezena6. One of the questions is "Which was the most drawn ball?"

SELECT COUNT(dezena5), dezena5 as bola FROM megasena
GROUP BY dezena
UNION 
SELECT COUNT(dezena6), dezena6 as bola FROM megasena
GROUP BY dezena6;

As for example the ball 1 can appear in all tens, I have to add up how many times it appeared in each dozen, and then all?
And as I present only the one who most appeared?

mysql sql

  • You said "the 1 ball can appear in all tens", where exactly do you take this data from? The "dozens" are the numbers?

  • Yes, there are ten numbers. Here is a print that can help: https://prnt.sc/116ikbt .

  • man, I was trying to tweak the code a little bit but I think that then you would have different queries, no? I mean, it would be a single query to bring total by ten, overall total and the one that most appeared, or would be different consultations?

  • The answer has to be the amount of times that the ball appeared and which was the ball that appeared the most, I mean, I have to count each ball in each ten and then add the count for equal balls, and present among those balls which was the one that has the highest count.

  • I am without having to write a detailed and documented reply, but see a solution here https://paiza.io/projects/uhYZVnH3pYVdoUhshBQ7g?language=mysql

  • Thank you very much, that’s exactly it!!!

Show 1 more comment

1 answer

0

The answer is as follows:

  1. Unify the value of all tens, but instead of UNION use UNION ALL, because UNION when there are repetitions considers only once the value, remember the lessons of set theory A U B? Database is set theory :) https://www.todamateria.com.br/teoria-dos-conjuntos/

  2. Give an alias (nickname), for the nineteen column of the first select, because in doing this it will become the name of the column, which will represent the values generated through the UNIONS, that is, only it needs a nickname;

  3. Apply Count(*), grouping through the "ball" field, UNIONS result;

  4. Sort the Count(*) result of each ball in descending order (Higher to Lower);

  5. Finally limit the result;

This format is cool because it makes your query more flexible, manipulating the limit, you could return for example the 5 most drawn balls, the 10+ and etc...

I hope I helped, and if I win at the Mega-Sena, :)

  
  select bola, count(*) numero_vezez
    from (
    SELECT dezena1 as bola
     FROM megasena
    UNION ALL 
    SELECT dezena2
     FROM megasena
    UNION ALL
    SELECT dezena3
     FROM megasena
    UNION ALL
    SELECT dezena4
     FROM megasena
    UNION ALL
    SELECT dezena5
     FROM megasena
    UNION ALL
    SELECT dezena6
     FROM megasena
    ) bola
    group by bola
    order by count(*) desc
    limit 1

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.