[thelist] What's wrong with my query?

Phil Turmel pturmel-webdev at turmel.org
Thu May 17 15:09:28 CDT 2007


Hi Casey,

Casey Crookston wrote:
> 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?
> 
Yes.

You don't need a subquery here.  Just the grouping (I left off the
'order by' as its unclear what you're after):

SELECT business_name, SUM(points_awarded)
FROM vw_points_awarded
GROUP BY business_name

HTH,

Phil



More information about the thelist mailing list