Join Statements - 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 2000 Forums

SQL Server Development (2000)

Join Statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-01 : 08:09:56

Kizito writes "I have a table the contains wage data. The wage data contains the classifications of the different jobs. Each job classification is either training or nor training. If the job is training, then the field training ='Y'. If not training ='N'. In this same table is the total hours that the employee completed for the week, field =totalhours worked. What I want to return is a a recordset that shows the total hours worked for employess whose training status ='Y' AND the totalhours worked for an employee whose training status ='N'

So far I a SQL statement:

SELECT SUM(totalhoursworked) ASallhours, JourneyLevel, craft, company
FROM WageData
WHERE training ='Y' GROUP BY Craft, JourneyLevel, company

This works, but it doesn not show me the total hours worked for employees whose training ='N' I want to see the total hours for training ='Y' and training ='N' in the same recordset.

Please help!"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-01 : 08:11:52

SELECT SUM(totalhoursworked) ASallhours, JourneyLevel, craft, company
FROM WageData
WHERE training in ('Y','N') GROUP BY Craft, JourneyLevel, company, training

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-01 : 09:13:39

not sure, but as a slight modification to Corey's code, you might want this:


SELECT JourneyLevel, craft, company, 
  CASE(SUM WHEN Training='Y' THEN Hours Else 0 END) as TrainingYHours,
  CASE(SUM WHEN Training='N' THEN Hours Else 0 END) as TrainingNHours, 
  SUM(totalhoursworked) AS Allhours
FROM 
  WageData
WHERE 
  training in ('Y','N') 
GROUP BY Craft, JourneyLevel, company, training

This way you can see the grand total, plus the hours for Y or N individually on the same line. Learn this basic technique and tweak it as needed.

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-01 : 09:23:17

Hmmm... Looks quite similar to this post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45366

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources