CODEWITHSUNDEEP
mysqlsql
user10641275
user10641275

Reputation:

Group by with sum doesn't return correct result

Say a table has this schema :

grp   |   number
1     |     10    
1     |     10    
1     |     10    
2     |     30    
2     |     30    
3     |     20

Note that each unique grp has a unique number even if there are more than 1 grp. I'm looking to sum all numbers for each unique grp.

So I want to group my table by grp to have this :

grp   |   number
1     |     10    
2     |     30    
3     |     20

And then get the sum which is now 60, but without grouping it gets me 110 as it calculates the sum of everything without grouping. All in one query, with no sub-queries if possible.

I've tried doing the following :

SELECT sum(number) as f
FROM ...
WHERE ...
GROUP BY grp

But this doesn't work, it returns multiple results and not the single result of the sum. What am I doing wrong?

Upvotes: 0

Views: 822

Answers (2)

ADyson
ADyson

Reputation: 61977

If you group by the group, then you'll get one result for each group. And it won't take into account the fact that you only want to use the value from each group once.

To get your desired result, taking one row from each group, you first need to make a subquery selecting DISTINCT group/number combinations from the table, and then SUM that.

SELECT 
  sum(`number`) as f
FROM 
  (SELECT DISTINCT `grp`, `number` FROM table1) g

This will output 60.

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a3b346041731a4b4c85f4e151c10f70

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use subquery to select unique records & do the sum:

select sum(number)
from (select distinct grp, number
      from table t
     ) t;

Upvotes: 4

Related Questions