Case Code Statement and Average - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Case Code Statement and Average

Case Code Statement and Average

Options

Previous Topic · Next Topic

Dona

Posted: Monday, October 08, 2007 4:17:11 PM

Rank: Newbie

Joined: 10/8/2007
Posts: 3
Points: -91
Location: US

Please help.
The sum statements work; however, when I add the GPA statements, this does not work.
Any suggestions. I will need averages for other fields.

SELECT Term, Sum(CASE when apc Not Like "YN" then 1 else 0 end) AS Applicants,
avg(CASE when GPA = apc Not Like "YN" then gpac end)as AvgApplicantGPA,
Sum(CASE when apc like "A%" or apc like "S%"
then 1 else 0 end)AS Admits, Avg(CASE when apc like "A%" or apc
or apc like "S%" then GPAC)AS AvgGPA Admits
FROM TEMP
GROUP BY Term;
quit;

This is a sample of the results I want.

Term APPS App Admit
GPA Admits GPA
1990 29 3.69 19 3.89
2000 31 3.59 17 3.82
2001 36 3.83 23 4.03

Thank you.!

Scott Whigham

Posted: Wednesday, October 10, 2007 10:43:44 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Dona, there's a lot wrong with that SQL Smile

No offense but you've got a mess there hehe Big Grin

Anyway, your primary problem here is one of syntax: AVG(CASE WHEN GPA = apc NOT LIKE "YN" THEN gpac END) is not valid SQL. The problem is here: GPA = apc NOT LIKE "YN". What are you trying to say? It might be GPA = apc AND apc NOT LIKE "YN" but I'm not sure.

Dona

Posted: Wednesday, October 10, 2007 11:36:24 AM

Rank: Newbie

Joined: 10/8/2007
Posts: 3
Points: -91
Location: US

Scott... thanks for your honesty....Any suggestions?
I want the average GPA for applicants and the average GPA for admits. However, I needed to define applicants and admits first.

Thank you.

Scott Whigham

Posted: Wednesday, October 10, 2007 12:08:54 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Make sure these are correct:

Code:

SELECT Term
  , SUM(CASE WHEN apc != 'YN' THEN 1 ELSE 0 END) AS Applicants
  , AVG(CASE WHEN GPA = apc AND apc NOT LIKE 'YN' THEN gpac END) AS AvgApplicantGPA
  , SUM(CASE WHEN apc LIKE 'A%' OR apc LIKE 'S%' THEN 1 ELSE 0 END) AS Admits
  , AVG(CASE WHEN apc LIKE 'A%' OR apc LIKE 'S%' THEN GPAC) AS [AvgGPA Admits]
FROM TEMP
GROUP BY Term;

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.