[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How to Count Averages? Newbie Needs Help

Chamark via WebmasterKB.com

3/21/2007 3:53:00 PM

Is there a way to count averages all in one Select statement? Obviously I am
a newbie at this, so here is what I am attempting to do. My table is made up
of 3 columns, StudentName, TestDate, Score.

I want to average the students score between date 1 and date 2 and then count
those averages that are <= 0.06. I get lost on where the count part should be
(if it can be done)?

Select STUDENTNAME, avg(SCORE) AS [avg score]
FROM tlbAssessments
WHERE TestDate >= 'mm/dd/yyyy' AND TestDate <= 'mm/dd/yyyy'

Now I need to count averages <= 0.60

Any help is appreciated in advance....Thanks

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...
5 Answers

RLoski

3/21/2007 5:20:00 PM

0


select sum( case when [avg score] <= 0.06 then 1 else 0 end) as
CountAvgLTSixPercent,
sum( case when [avg score] <= 0.6 then 1 else 0 end) as CountAvgLTSixtyPercent

from
(
Select STUDENTNAME, avg(SCORE) AS [avg score]
FROM tlbAssessments
WHERE TestDate >= 'mm/dd/yyyy' AND TestDate <= 'mm/dd/yyyy'
group by STUDENTNAME
) a

--
Russel Loski, MCSD.Net

masri999

3/21/2007 6:12:00 PM

0

On Mar 21, 8:53 pm, "Chamark via SQLMonster.com" <u21870@uwe> wrote:
> Is there a way to count averages all in one Select statement? Obviously I am
> a newbie at this, so here is what I am attempting to do. My table is made up
> of 3 columns, StudentName, TestDate, Score.
>
> I want to average the students score between date 1 and date 2 and then count
> those averages that are <= 0.06. I get lost on where the count part should be
> (if it can be done)?
>
> Select STUDENTNAME, avg(SCORE) AS [avg score]
> FROM tlbAssessments
> WHERE TestDate >= 'mm/dd/yyyy' AND TestDate <= 'mm/dd/yyyy'
>
> Now I need to count averages <= 0.60
>
> Any help is appreciated in advance....Thanks
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-progra......

Select STUDENTNAME, avg(SCORE) AS [avg score]
FROM tlbAssessments
WHERE TestDate >= 'mm/dd/yyyy' AND TestDate <= 'mm/dd/yyyy'
group by STUDENTNAME having avg(SCORE) <= 0.60

Gert-Jan Strik

3/21/2007 7:57:00 PM

0

Try this:

SELECT COUNT(*) AS "Number of students with low average"
FROM (
SELECT StudentName
FROM tblAssessment
WHERE TestDate >= '20070320'
AND TestDate < '20070321'
GROUP BY StudentName
HAVING AVG(Score) <= 0.60
) T

HTH,
Gert-Jan

"Chamark via SQLMonster.com" wrote:
>
> Is there a way to count averages all in one Select statement? Obviously I am
> a newbie at this, so here is what I am attempting to do. My table is made up
> of 3 columns, StudentName, TestDate, Score.
>
> I want to average the students score between date 1 and date 2 and then count
> those averages that are <= 0.06. I get lost on where the count part should be
> (if it can be done)?
>
> Select STUDENTNAME, avg(SCORE) AS [avg score]
> FROM tlbAssessments
> WHERE TestDate >= 'mm/dd/yyyy' AND TestDate <= 'mm/dd/yyyy'
>
> Now I need to count averages <= 0.60
>
> Any help is appreciated in advance....Thanks
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...

Chamark via WebmasterKB.com

3/21/2007 9:29:00 PM

0

You have assisted me before. As always you are the best. I didn't understand
the closing )T. I know it is a DERIVEDTBL. Anyway all is great. Thanks so
much!!!

Gert-Jan Strik wrote:
>Try this:
>
> SELECT COUNT(*) AS "Number of students with low average"
> FROM (
> SELECT StudentName
> FROM tblAssessment
> WHERE TestDate >= '20070320'
> AND TestDate < '20070321'
> GROUP BY StudentName
> HAVING AVG(Score) <= 0.60
> ) T
>
>HTH,
>Gert-Jan
>
>> Is there a way to count averages all in one Select statement? Obviously I am
>> a newbie at this, so here is what I am attempting to do. My table is made up
>[quoted text clipped - 15 lines]
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...

Colonel Burke

7/29/2014 2:08:00 PM

0

"Nicodemus" <Ancient-of-Days@Heaven.Net> wrote in message
news:XnsA378968C1FE5CGoodNewsHorizonnet@94.75.214.90...
> "Michael Christ" <jesusisthelordofall@hotmail.com> wrote in
> news:lr5gjb$2gv7$1@adenine.netfront.net:
>
>> It is very very bad the way these characters behave on these
>> newsgroups.
>>
>> Namely (alphabetically), I'll let the Lord decide just who is the
>> evilest...
>>
>> Dolf (the intellectual idiot)

That's another reason I like Dolf. He's not just physically attractive;
he's a mental whizz too!