Re: SQL Question re group averaging

On 12 Jun 2001 10:24:01 -0700, jdriller_at_orchid.org (Jonathan Driller) wrote:

>I finally realized that I need to set an alias to use aggregate
>functions but still can't get what I need:
>I have a table of rankings for lots of products. I want to be able to
>say 'this
>product had 20 people rank it and the average rank is 7".
>If I say select sum(srating) then I sum every product together so I
>figure I need some where clause, but I can't figure out how to do
>this.
>
>I am using Cold Fusion so I can pass in a dynamic value If I wanted to
>get this for one item - but I want to output a list of ALL items and
>their values separately...
>'where prodid = #prodid#'
>
>Any help much appreciated.

SELECT

		prodid, 
		count( rank ), 
		avg( rank )
	FROM
	WHERE
		prodid IN ( ... )
	GROUP BY
		prodid
	ORDER BY
		prodid

Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada
-- 
Brian.Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply
tosspam_at_aol.com abuse_at_aol.com abuse_at_yahoo.com abuse_at_hotmail.com abuse_at_msn.com abuse_at_sprint.com abuse_at_earthlink.com abuse_at_cadvision.com abuse_at_ibsystems.com uce_at_ftc.gov
						spam traps