How to group by and sum in sql

I have tables like following one I’d like to extract customers who has product=a

customer product score
A          a      10
A          b      20 
A          c      30
B          b      10 
B          c      20
C          a      30
C          c      40

And then, I’d like to sumthe score by each customer.

customer product score
A          a      10
A          b      20 
A          c      30
C          a      30
C          c      40

Therefore my desired result is like following Are there any way to achieve this?

customer score
A         60
C         70

My work is like below. I’d like to know next step

SELECT t1.customer, t1.product, t1.score
  FROM yourTable t1
 WHERE EXISTS (SELECT 1
                 FROM yourTable t2
                WHERE t2.customer = t1.customer
                  AND t2.product = 'a');

Thanks

Answer

You would need an aggregate (GROUP BY) and a SUM(t1.score)

SELECT t1.customer, sum(t1.score) as summed_score
  FROM yourTable t1
WHERE EXISTS (SELECT 1 
                FROM yourTable t2 
               WHERE t2.customer = t1.customer 
                 AND t2.product = 'a')
GROUP BY t1.customer

Advertisement

Answer