Help with Query

Question

Post reply

Help with Query

dheer

Ten Centuries

Points: 1275
More actions
July 14, 2010 at 8:02 am

#223387

I have a table Below ...
Name DOB MathsScienceChemistyPhysics
RAM 11/2/1986 00 0 45
RAM 12/5/1985 25 0 0 0
ANTHONY07/6/1981 250 0 0
RAM 11/2/1986 250 0 0
RAM 11/2/1986 00 85 0
ANTHONY07/6/1981 050 0 0
ANTHONY07/6/1981 00 75 0
I want the result as
Name DOB MathsScienceChemistyPhysics Total
RAM 11/2/1986 25 0 85 45 155
Anthony 07/6/1981 25 50 75 0 150

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

looks like school homework to me, therefore no solution but a guide:

Read about using aggregate functions in SQL, in your case it would be SUM.

Also, please click the link in my signature to learn how to post your question in forum's polite way.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Also, since you're new, please study the article at the first link in my signature line below. People will trip over each other to help you if you do what the article says.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

Hello dheer,

Please thy the following select query

select

name,

MAX(dob), MAX(maths), MAX(science), MAX(chemisty), MAX(physics),

MAX(maths) + MAX(science) + MAX(chemisty) + MAX(physics)

from lessons

group by name

I hope that helps you

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Eralper (7/15/2010)
Hello dheer,
Please thy the following select query
select
name,
MAX(dob), MAX(maths), MAX(science), MAX(chemisty), MAX(physics),
MAX(maths) + MAX(science) + MAX(chemisty) + MAX(physics)
from lessons
group by name
I hope that helps you

The problem with that is if there is more than one entry for any given subject. And, yeah... I know... the OP provided no such data but it will happen and the OP needs to be ready for it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 5

Hi Jeff,

Actually the sample data provided has more than one record for RAM and for Maths

It is not explicitly stated but, I chosed using MAX perhaps and AVG could be better.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams