CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Vicky Salunkhe
Vicky Salunkhe

Reputation: 10985

How to rank by counting data in multiple rows in a column with certain conditions on it in MySQL

I Have a table something like below

edit 1 column id is the primary key

id  ref_id  count_value  
10     34       5
11     34       2
12     36       3
13     30       1
14     25       20
15     34       15
15     36       10

what I want is to align and fetch the data in such a manner where

the value in count_value field will be add up for each corresponding ref_id

so here in the example

ref_id 34 have three entries and total count_value of 22

ref_id 36 have two entries and total count_value of 13

ref_id 25 have one entry and total count_value of 20

so that I am expecting is to be in this manner

ref_id
 34
 25
 36
 30

I tried using group by but that isn't going to solve this I guess as I want to add up the value present inside cell and then rank it up according to the final count

regarding the condition part in the question there is a timestamp column and will need to get only that data which is created after certain datetime

Upvotes: 0

Views: 67

Answers (1)

GMB
GMB

Reputation: 222482

You can group by ref_id, and then order the records by descending sum() of count_value:

select ref_id
from mytable
group by ref_id
order by sum(count_value) desc

You can add a where clause to the query to implement the filter on the timestamp column (which you did not show in your sample data): it goes between the from clause and the group by clause.

Demo on DB Fiddle:

| ref_id |
| -----: |
|     34 |
|     25 |
|     36 |
|     30 |

Upvotes: 1

Related Questions