[thelist] What's wrong with my query?

[Casey Crookston]

I have an mssql table that looks something like this:

 

ID            Business_Name               Points Awarded

----------------------------------------------------------

1              tom and jerry                    15

2              tom and jerry                    75

3              tom and jerry                    20

4              abbott and costello        5

5              abbott and costello        90

 

I'd like to return this result:

 

tom and jerry                    110

abbot and costello          95

 

Here's my query, which is not returning correct results:

 

SELECT     a.business_name, 

(SELECT SUM(points_awarded) FROM vw_points_awarded WHERE business_name =
a.business_name) AS points_awarded

FROM         vw_points_awarded a

WHERE     a.supplier_id = 1001

GROUP BY a.business_name 

ORDER BY a.business_name

 

 

I also tried:

 

SELECT     DISTINCT a.business_name, 

(SELECT SUM(points_awarded) FROM vw_points_awarded WHERE business_name =
a.business_name) AS points_awarded

FROM         vw_points_awarded a

WHERE     a.supplier_id = 1001

ORDER BY a.business_name

 

This should be simple, right?

 

Casey