CODEWITHSUNDEEP
phpmysqlsql
hanji
hanji

Reputation: 357

MySQL sum of distinct rows

I have a query performing INNER JOINs pulling in a relational cross reference table. So based on the query, I'm getting all (one to many) 'fruits' related to a basket. I need to sum the amount of fruits distinctly.

| Banana | 1 |
| Banana | 1 |
| Apple  | 1 |
| Pear   | 1 |
| Pear   | 1 |
| Pear   | 1 |

I want the result set to look like this...

| Banana | 2 | 
| Apple  | 1 |
| Pear   | 3 |

I tried to SUM() fruit in the SELECT with a GROUP BY but the results were not correct (way off). I'm thinking HAVING might need to come into play... anyway, frustrated since this should be pretty easy.

Upvotes: 1

Views: 155

Answers (3)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

If there are only 1's in the second column, try:

SELECT fruit, count(fruit)
FROM fruits_table
GROUP BY fruit

Upvotes: 1

Rostand Richard
Rostand Richard

Reputation: 31

Try this code:

select fruit_name, sum(qte) 
from fruit_table_name
group by fruit_name;

Upvotes: 0

Tricky12
Tricky12

Reputation: 6822

Not too difficult with a SUM() and GROUP BY:

select a.fruit, sum(a.num) as number
from fruits a
group by a.fruit;

SQLFiddle: http://sqlfiddle.com/#!2/3b53a/1

Upvotes: 0

Related Questions