Catch the lowest and highest value of a category with the count and ID

Asked

Viewed 39 times

1

I need some help to make a SELECT that returns me the highest and lowest value of a category with its respective IDs and the amount of each category. The algorithm is complex, because it needs to return in a single line the result.

Can someone help me?

I have this table:

ID | CAT | VALOR


1  |  A  | 153

2  |  B  | 100

3  |  A  | 26

4  |  A  | 65

5  |  B  | 23

6  |  A  | 161

7  |  B  | 150

8  |  A  | 30

9  |  C  | 50

And I need this comeback:

ID | CAT | QTDE_CAT | MENOR_VALOR | ID_MENOR_VALOR | MAIOR_VALOR | ID_MAIOR_VALOR

1  |  A  |    5     |   26        |      3         |    161      |      6

2  |  B  |    3     |    23       |      5         |    150      |      7

3  |  C  |    1     |    50       |      9         |    50       |      9
mysql sql database select mariadb
  • 2

    What is this first ID field of the result?

  • In the case of equality in the lowest or highest value for different ID what should be listed?

  • The first ID field can be ignored, it’s just a sequence. In the case of equality would be the example of categiria C where the highest and lowest value would be equal

  • I meant for the same category there are different id with the same lower and/or higher value.

  • 1

    I do not know if I understood your doubt, but in reality each line has the quantity of a certain category. The Ids of this entry table are unique and different for each row.From this entry table, you would have to make an output summary showing a row for each category with its minimum and maximum values and showing what the ID of these minimum and maximum values is. also the total of each category

  • I made a solution, I think it will suit you, take the test!

  • 1

    Unbelievable. Thank you very much, I don’t know what to say. It worked perfectly at first. Thank you very much. You really know the subject and it’s out of the curve your knowledge. Even worth!!!!

  • Edson pointed out the answer to his question

Show 3 more comments

1 answer

0


The first column has been set aside as stated in your comment that can be ignored, so to have the same question result, you have to work with Subconsultation to have the Id of Valor Minor and Greater, example:

SELECT 
 COUNT(a.valor) QTDE_VALOR,
 MIN(a.valor) MENOR_VALOR, 
 MAX(a.valor) MAIOR_VALOR,           
 (SELECT b.ID FROM source b WHERE MIN(a.VALOR)=b.valor and a.CAT=b.CAT) ID_MENOR_VALOR, 
 (SELECT b.ID FROM source b WHERE MAX(a.VALOR)=b.valor and a.CAT=b.CAT) ID_MAIOR_VALOR,
 a.CAT
FROM source a
GROUP BY CAT

Online Result: Sqlfiddle

Browser other questions tagged mysql sql database select mariadb

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