query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

hugh_mungo
Starting Member

10 Posts

Posted - 2008-04-04 : 07:18:27

I have the following table:

id idhead caption heading ascore maxscore na
1 1 htext1 1 0 0 0
2 1 qtext1 0 3 10 0
3 1 qtext2 0 5 5 0
4 1 qtext3 0 0 8 1
5 2 htext2 1 0 0 0
6 2 qtext1 0 4 8 0

THe table holds headings (heading = 1), questions (heading = 0), actual scores (ascore), maximum possible score (maxscore) and whether the questions was applicable (na = 1 indicates not applicable)

Questions are grouped under a heading with the same idhead.

I am trying the write a sql statement to extract the caption of the headings with the sum of the actual scores and the sum of the maximum scores (excluding the ones where na = 1)

so I would get the following

heading max poss score actual score
htext1 15 8
htext2 8 4

Problem is I can't figure out how to do it.

Any help would be fantastic.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-04 : 08:50:19

If I may suggest...

The query will be much simpler if you were to split the header out into a seperate table.
So, something like this.

CREATE TABLE QuestionaireHeaders (
HeaderID INT IDENTITY PRIMARY KEY,
HeaderCaption VARCHAR(200)
)

CREATE TABLE QuestionaireQuestions (
QuestionID INT IDENITIY PRIMARY KEY,
HeaderID INT,
Question VARCAHR(200),
ActualScore SmallINT,
MaxScore SmallINT,
NotApplicable BIT
)

Then your query will be a very simple
SELECT HeaderCaption, Sum(ActualScore) AS TotalActual, Sum(MaxScore) AS TotalMaximum
FROM QuestionaireHeaders qh inner join QuestionaireQuestions qq
ON qh.HeaderId = qq.headerID
WHERE NotApplicable = 0
Group By HeaderCaption

Gail Shaw

Have a nice day, unless you have other plans

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-04 : 08:55:51

If you prefer to leave the table structure as is, it's a little more work.

SELECT caption, MaxPossibleScore, totalScore FROM
(SELECT ihead, caption FROM Questionaire WHERE heading = 1) AS Heading
Inner Join
(SELECT ihead, sum(ascore) as totalScore, sum(maxscore) as MaxPossibleScore FROM Questionaire WHERE heading = 0 and na = 0 group by ihead) as Questions
on Heading.ihead = Questions.ihead

Gail Shaw

Have a nice day, unless you have other plans

hugh_mungo
Starting Member

10 Posts

Posted - 2008-04-04 : 09:12:43

Excellent - thanks very much

Have a good weekend

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 12:35:23

or even this:-

SELECT MAX(CASE WHEN heading=1 THEN caption ELSE NULL END) as heading,
       SUM(CASE WHEN heading=0 THEN maxscore ELSE 0 END) as maxpossscore,
       SUM(CASE WHEN heading=0 THEN ascore ELSE 0 END) as actualscore
FROM Questionaire
GROUP BY idhead

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources