Good afternoon all! Ok, I need o 1

[TZyk2003]

Good afternoon all!

Ok, I need one query to do something rather complicated. Break out your SQL skills!

I have 3 tables.

Table 1 = Project
Table 2 = Project_Test_CB
Table 3 = Project_Test_BDR

The 'Project' table has a primary key named "PMT_No".
Tables 2 and 3 have further information about each "PMT_No" so they are each linked with an inner join on PMT_No on Project. Note: Project simply has ALL the "PMT_No"s...one might or might not be in Table 2 or Table 3.


Next we have "Pass_CB" (located in Table 2) and "Pass_BDR" located in Table 3. These combined with PMT_No is a primary key for Table 2 and 3 respectively.

Finally, we have "Test_Cond_CB" (located in Table 2) and "Test_Cond_BDR" (located in Table 3). These fields are Integers.

I need a SQL statement that will SUM the "Test_Cond_CB" field AND SUM the "Test_Cond_BDR" fields for each Pass AND THEN ADD that result together. Example: For EACH PMT_No (34B, 26E, and 56Y for example) add up the "Test_Cond_CB" for each Pass. Same thing for "Test_Cond_BDR". THEN ADD "Test_Cond_BDR" and "Test_Cond_CB" together for each Pass.

More examples: Say PMT_NO is "AM1234". Pass_CB and Pass_BDR are both 1. And "Test_Cond_BDR" is 5 while "Test_Cond_CB" is 10.

The final results should be:
Pass SumOfTestConditions
1 15


So I need to group by Pass.

Here are the tables and attributes in case you didn't follow:

"Project" Table:
PMT_No (PK)

"Project_Test_CB" Table:
PMT_No (PK)
Pass_CB (PK) (possible values are only 1, 2, or 3)
Test_Cond_CB (an integer)

"Project_Test_BDR" Table:
PMT_No (PK)
Pass_BDR (PK) (possible values are only 1, 2, or 3)
Test_Cond_BDR (an integer)

Does anyone know how to do this??? I've been working on this all morning but to no avail. I need someone bigger than I to answer this.

THANKS!!

-Tim

 

[TZyk2003]

Sorry about the topic! It won't let me edit my post after I sent it!

 

[rac2]

Here is an interesting fact: SUM(A) + SUM(B) = SUM(A+B).

It seems to me that the Project table is irrelevant to this problem.

If you group by Pass then your result will probably have three rows like this.
Pass SumOfTestConditions
1 15
2 9
3 29

SELECT 
       a.Pass,
       SUM(a.Test_Cond_CB + b.Test_Cond_BDR)
           AS "SumOfTestConditions"

FROM Project_Test_CB a
JOIN Project_Test_BDR b ON
       a.PMT_No = b.PMT_No 
   AND a.Pass_CB = b.Pass_BDR

This might work if you have complete data. By complete data I mean that every PMT_No has all three Pass_CB and all three Pass_BDR. If not then you may need to use the Project Table, outer joins, and recoding null values.

Give me more details about the completeness of your data and why the above is not the solution you need and I will take another look.

 

[TZyk2003]

It worked!! Thanks a million!

-Tim

 

[TZyk2003]

How about a follow up question for a star (or two)?

Ok, for the same database.

I have to do seperate queries for each pass since there is a report for each pass.

So, ALL the PMT_No's are in Project but they MAY OR MAY NOT be in "Project_Test_CB" Table or "Project_Test_BDR" Table (might in one, both, or neither).

How do I set this up while still grouping by Pass_CB and Pass_BDR and still summing up the Test Conditions?

My problem is getting the SQL the NOT group by Pass_CB if the PMT_No ISN'T in the "Project_Test_CB" table.

Many thanks!!

-Tim

 

[rac2]

Seems like there should have been a GROUP BY clause in that query. You must have supplied it.

For a report on the scores for the first pass which may or may not be completed for both tests this might work.

SELECT
       PMT_NO, 
       SUM(a.Test_Cond_CB + b.Test_Cond_BDR)
           AS "TestScore"

FROM Project c

LEFT JOIN Project_Test_CB a
       a.PMT_No = c.PMT_No 
   AND a.Pass_CB = 1

LEFT JOIN Project_Test_BDR b ON
       b.PMT_No = c.PMT_No 
   AND b.Pass_BDR =1

GROUP BY PMT_NO

In theory this will provide the sum of the CB and BDR test scores for each of the projects. The LEFT JOIN ensures there is a row for a project when the CB test is missing or when the BDR test is missing.

Actually you will need to recode nulls to 0 in order to get numbers for every project. Replace a.Test_Cond_CB with

IIf(IsNull(a.Test_Cond_CB), 0, a.Test_Cond_CB) and replace b.Test_Cond_BDR with

IIf(IsNull(b.Test_Cond_BDR), 0, b.Test_Cond_BDR)



Which raises the question, what is the meaning of a total score for pass 1 if both tests are not completed?



Assuming there is some meaning to that score . . .



Save this query with a name, ResultsPass1. Define and save similar queries for the other passes.

Define and save a UNION query; name it Results.

SELECT PMT_NO, '1' AS "Pass", TestScore
FROM ResultsPass1

UNION

SELECT PMT_NO, '2', TestScore
FROM ResultsPass2

UNION

SELECT PMT_NO, '3', TestScore
FROM ResultsPass3

Then generate a report with this query.

SELECT
       Pass,
       SUM(TestScore)
FROM Results
GROUP BY Pass

Whew! No more, please!