CODEWITHSUNDEEP
mysqlsqlselect
Matt Zho
Matt Zho

Reputation: 1

Selecting multiple same value rows out of the same column

I have a table, where one of the columns is named mid. It has a lot of values, some of them repeat themselves. Theres also a column named chashrate. It has a different value for each mid row. Theres also a column named pid, which shows the id of each row.

I've tried pulling out specific value rows with HAVING, but I can only do one value at a time or multiple values that dont match each other

$miner = $pdo->query("SELECT * FROM data WHERE pid='6'")->fetchall();

What I need to do is collect all the same MID column value rows, with the id pid=6 so for example all of the mid = 8; pid=6, collect their chashrate and sum it up. So for example I would get mid(8)=17394, mid(6)=28424 etc.

Here's a photo of the table: https://i.sstatic.net/GRs9X.png

The same colored rows need to be selected and their chashrate values summed up.

Upvotes: 0

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You seem to want aggregation:

select mid, sum(chashrate) as sum_chashrate
from data
where pid = 6
group by pid, mid;

This will return multiple rows, one for each mid value.

You can do this for multiple pids -- or even all of them, by removing or changing the where clause.

Upvotes: 0

Claudio
Claudio

Reputation: 5203

Try using SUM to sum the cashrate values and GROUP BY to group them by mid.

SELECT mid
     , SUM(`cashrate`) AS total
FROM `data`
WHERE pid = 6
GROUP BY mid;

Check it here.

For the given data on the image, this query will output the following result:

mid | total
6   | 981
8   | 374

Upvotes: 1

Related Questions