[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