There are 3 tables I need to get info from. The "Programs" table contains general program information, the "Part_List" table contains the list of parts for all of the programs and the "Approval" table contains information related to each part's approval status:

Programs
--------
ProgramID
Program_Name
...
...

Part_List
---------
ProgramID
Part_Name
Vendor
...
...

Approval
--------
Part_Name
Vendor
Approval (value of 'x' if approved, NULL if not)
...
...

Here is an example query that returns the list of programs, along with the total number of parts in each program.

SELECT Programs.Program_Name, COUNT(*) as number_of_parts
FROM Programs INNER JOIN
Part_List ON Programs.ProgramID = Part_List.ProgramID
LEFT OUTER JOIN
Approval ON Part_List.Part_Name = Approval.Part_Name
AND Part_List.Vendor = Approval.Vendor
GROUP BY Programs.Program_Name

What I need to do is add a column to the query above, displaying the number of approved parts for each program, so a percentage can be calculated.

Any help would be greatly appreciated.

Hi, i have a similar problem.
I want to execute a query that returns a table with this fields:
player_name | total_goals | total_yellow_cards | total_red_cards

Tables are:

1) MATCHES (codMatch | seasonMatch)
2) SCORERS (codMatch | playerName | playerTeam)
3) CARDS (codMatch | playerName | cardType)

With this query:

select playerName, sum(case when cardType = 'Y' then 1 else 0 end) as totYellow, sum(case when cardType = 'R' then 1 else 0 end) as totRed
from cards left outer join matches on cards.codMatch=matches.codMatch
where matches.seasonMatch=2010
group by playerName
order by playerName

i obtained a table with stats about cards. Similarly i did for the scorers.

But what i have to do to obtain a single table that contains all stats (scorers + cards) ?

Thank you in advance.

Thanks to Giuseppe Loccisano I think you have your answer

SELECT Programs.Program_Name, COUNT(*) as number_of_parts, SUM(CASE WHEN Approval = 'x' then 1 ELSE 0 END) as number_approved
FROM Programs INNER JOIN
Part_List ON Programs.ProgramID = Part_List.ProgramID
LEFT OUTER JOIN
Approval ON Part_List.Part_Name = Approval.Part_Name
AND Part_List.Vendor = Approval.Vendor
GROUP BY Programs.Program_Name

This reply was removed on 2/20/2012 9:14:14 AM.
See the changelog