MySQL How to create this subquery?

Question

I have the following tables

Farm table

+---------+--------+-------------------+-----------+------------+ | FARM_ID |Stock_ID| FARM_TITLE | Size | FARM_VALUE | +---------+--------+-------------------+-----------+------------+ | 2 | 1 | AgriZone | M | 202 | | 3 | 1 | Cow Mill | L | 11 | | 4 | 2 | Beef Farm | H | 540 | | 5 | 2 | CattleOne | M | 1080 | | 6 | 2 | FarmOne | L | 455 | | 7 | 3 | Perdue | H | 333 | | 8 | 4 | Holstein | M | 825 | | 10 | 1 | Dotterers | H | 98 | +---------+--------+-------------------+-----------+------------+ 

Gate table

 +---------+---------+------------+ | GATE_ID | FARM_ID | FARM_VALUE | +---------+---------+------------+ | 1 | 2 | 0 | | 1 | 3 | 0 | | 1 | 4 | 540 | | 2 | 4 | 550 | | 3 | 4 | 560 | | 4 | 4 | 570 | | 5 | 4 | 580 | | 6 | 4 | 590 | | 1 | 5 | 1080 | | 2 | 5 | 1100 | | 3 | 5 | 1120 | | 4 | 5 | 1140 | | 5 | 5 | 1160 | | 6 | 5 | 1180 | | 1 | 6 | 455 | | 2 | 6 | 536 | | 3 | 6 | 617 | | 4 | 6 | 698 | | 5 | 6 | 779 | | 6 | 6 | 860 | | 1 | 7 | 0 | | 1 | 8 | 0 | | 1 | 10 | 0 | +---------+---------+------------+ 

Table origin

 +--------+----------+ | ORI_ID | ORI_NAME | +--------+----------+ | 1 | US | | 2 | CA | | 3 | MX | +--------+----------+ 

Table

 +--------+--------+-------------------+ |Stock_ID| ORI_ID | Stock_TITLE | +--------+--------+-------------------+ | 1 | 1 | P1 | | 2 | 2 | P3 | | 3 | 3 | Q4 | | 4 | 3 | B3 | +--------+--------+-------------------+ 

Table results

 +-----------+---------+---------+------------+------------+ | RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% | +-----------+---------+---------+------------+------------+ | 1 | 7 | 1 | 333 | 100 | | 2 | 8 | 1 | 825 | 100 | | 3 | 6 | 1 | 455 | 40 | | 4 | 6 | 2 | 536 | 0 | | 5 | 6 | 3 | 617 | 0 | | 6 | 6 | 4 | 698 | 100 | | 7 | 6 | 5 | 779 | 0 | | 8 | 6 | 6 | 860 | 10 | | 9 | 4 | 1 | 540 | 100 | | 10 | 4 | 2 | 550 | 90 | | 11 | 4 | 3 | 560 | 0 | | 12 | 4 | 4 | 570 | 100 | | 13 | 4 | 5 | 580 | 10 | | 14 | 4 | 6 | 590 | 0 | | 15 | 5 | 1 | 1080 | 0 | | 16 | 5 | 2 | 1100 | 0 | | 17 | 5 | 3 | 1120 | 0 | | 18 | 5 | 4 | 1140 | 50 | | 19 | 5 | 5 | 1160 | 0 | | 20 | 5 | 6 | 1180 | 100 | | 21 | 3 | 1 | 11 | 100 | | 22 | 10 | 1 | 98 | 90 | | 23 | 2 | 1 | 202 | 100 | +-----------+---------+---------+------------+------------+ 

Annotated results table: same as above ^

 +-----------+---------+---------+------------+------------+ | RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% | +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 1 | 7 | 1 | 333 | 100 | <--|H-Case {H} +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 2 | 8 | 1 | 825 | 100 | <--|M-Case {M} +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 3 | 6 | 1 | 455 | 40 | | 4 | 6 | 2 | 536 | 0 | | 5 | 6 | 3 | 617 | 0 | | 6 | 6 | 4 | 698 | 100 | <--|L | 7 | 6 | 5 | 779 | 0 | | | 8 | 6 | 6 | 860 | 10 | | +-----------+---------+---------+------------+------------+ | | 9 | 4 | 1 | 540 | 100 | | | 10 | 4 | 2 | 550 | 90 | | | 11 | 4 | 3 | 560 | 0 | | | 12 | 4 | 4 | 570 | 100 | <--+M-case {H,M,L} | 13 | 4 | 5 | 580 | 10 | | | 14 | 4 | 6 | 590 | 0 | | +-----------+---------+---------+------------+------------+ | | 15 | 5 | 1 | 1080 | 0 | | | 16 | 5 | 2 | 1100 | 0 | | | 17 | 5 | 3 | 1120 | 0 | | | 18 | 5 | 4 | 1140 | 50 | <--|H | 19 | 5 | 5 | 1160 | 0 | | 20 | 5 | 6 | 1180 | 100 | +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 21 | 3 | 1 | 11 | 100 | <--|L | 22 | 10 | 1 | 98 | 90 | <--+H-case {H,M,L} | 23 | 2 | 1 | 202 | 100 | <--|M +-----------+---------+---------+------------+------------+ 

Required Calculations:

• A type can have no more than three values: {H, M, L};
• When all values ​​are present, they are evaluated as follows: H = 70 M = 20 L = 10

• ALL unique cases are

• Case {H, M}: H = 80 M = 20

• Case {M, L}: M = 60 L = 40
• Case {H, L}: H = 90 L = 10
• Case {H}: H = 100
• Case {M}: M = 100
• Case {L}: L = 100
• Case {H, M, L}: H = 70 M = 20 L = 10

Further explanation

• Only a Share with at least one GATE fully satisfied can receive 100 points max.
  • Example: Q4 has 3 sets of 6 GATES ; Only one GATE set should be characterized (there is an estimate).
  • The given points should be multiplied by the specific case that it has. Example: Q4 has the case {H, M, L}, which means H = 70; M = 20; L = 10 This will result in (70 * 100%) + (20 * 50%) + (10 * 100%) = 90 (look back at the summary of the results table) 2.
• Points should be taken into account and taken into account even when the goal was not completely satisfied. Valves with MAX points received should be stored when the gate has not been fully satisfied. (Provides further explanation if not understood)

If we execute a query to define tables and data, it will look below

 +---------+-----------+---------------+-----------+---------+-----------+---------+ | Origin | Stock | Farm Title | Farm Value| Gate | Size | Score | +---------+-----------+---------------+-----------+---------+-----------+---------+ | US | P1 | Perdue | 333 | 1 | H | 100 | | US | P3 | Holstein | 825 | 1 | M | 100 | | CA | Q4 | FarmOne | 455 | 1 | L | 40 | | CA | Q4 | FarmOne | 536 | 2 | L | 0 | | CA | Q4 | FarmOne | 617 | 3 | L | 0 | | CA | Q4 | FarmOne | 698 | 4 | L | 100 | | CA | Q4 | FarmOne | 779 | 5 | L | 0 | | CA | Q4 | FarmOne | 860 | 6 | L | 10 | | CA | Q4 | Beef Farm | 540 | 1 | H | 0 | | CA | Q4 | Beef Farm | 550 | 2 | H | 90 | | CA | Q4 | Beef Farm | 560 | 3 | H | 0 | | CA | Q4 | Beef Farm | 570 | 4 | H | 100 | | CA | Q4 | Beef Farm | 580 | 5 | H | 10 | | CA | Q4 | Beef Farm | 590 | 6 | H | 0 | | CA | Q4 | CattleOne | 1080 | 1 | M | 0 | | CA | Q4 | CattleOne | 1100 | 2 | M | 0 | | CA | Q4 | CattleOne | 1120 | 3 | M | 0 | | CA | Q4 | CattleOne | 1140 | 4 | M | 50 | | CA | Q4 | CattleOne | 1160 | 5 | M | 100 | | CA | Q4 | CattleOne | 1180 | 6 | M | 0 | | MX | B3 | Cow Mill | 11 | 1 | L | 100 | | MX | B3 | Dotterers | 98 | 1 | H | 90 | | MX | B3 | AgriZone | 202 | 1 | M | 100 | +---------+-----------+---------------+-----------+---------+-----------+---------+ 

Wish Results

 +---------+-------------------+-------+ | Origin | Stock | score | +---------+-------------------+-------+ | US | P1 | 100 | | US | P3 | 100 | | CA | Q4 | 90 | | MX | B3 | 93 | +---------+-------------------+-------+ 

Since origin has a stock , which consists of 3 different farms , and those farms have 6 GATES each. As long as one GATE -set (a numerical match of GATES ) is evaluated to SOME value, we can fully count the entire stock . This is the only way to count stock 100.

Also, and repeat, stock Q4 has the case: {H, M, L} and all gate (4) were found to some extent. GATE 4 has an estimate of (100% * H) + (50% * M) + (100% * L), which is (70 * 100%) + (20 * 50%) + (10 * 100%) = 90

Therefore: (taken from above)

  | CA | Q4 | 90 | 

QED

So, I need help with creating a subquery / subheading to do this calculation. I installed everything in the script above (along with the query I was working on) in the SQL script link below.

Thanks a lot to the stackoverflow community.

> The above problem in SqlFiddle is here <

Answer 1

Here is the query I was working on. However, the results are slightly different from the results you posted in your question:

 select o.origin_name, s.stock_title, sum( case f.size when 'H' then case when sizes = 'H,L,M' then 70 when sizes = 'H,M' then 80 when sizes = 'H,L' then 90 when sizes = 'H' then 100 else 0 end when 'M' then case when sizes = 'H,L,M' then 20 when sizes = 'H,M' then 20 when sizes = 'L,M' then 60 when sizes = 'M' then 100 else 0 end else case when sizes = 'H,L,M' then 10 when sizes = 'L,M' then 40 when sizes = 'H,L' then 10 when sizes = 'L' then 100 else 0 end end * r.score / 100) FinalScore from farm f join ( select f.stock_id, group_concat(distinct f.size order by f.size) sizes from farm f join results r on f.farm_id = r.farm_id group by f.stock_id ) stockSizes on f.stock_id = stockSizes.stock_id join results r on f.farm_id = r.farm_id join ( select f.stock_id, r.gate_id from results r join farm f on r.farm_id = f.farm_id group by f.stock_id, r.gate_id having sum(r.score = 0) = 0 ) FullGates on FullGates.stock_id = f.stock_id and FullGates.gate_id = r.gate_id join stock s on s.stock_id = f.stock_id join origin o on o.origin_id = s.origin_id group by o.origin_id, s.stock_id 

Result:

 + ------------- + ------------- + ------------ +
 |  ORIGIN_NAME |  STOCK_TITLE |  FINALSCORE |
 + ------------- + ------------- + ------------ +
 |  US |  P1 |  93 |
 |  CA |  P3 |  90 |
 |  MX |  Q4 |  100 |
 |  MX |  B3 |  100 |
 + ------------- + ------------- + ------------ +

Let me know if this did the trick.

Answer 2

I would take your original query to get the second last table and change Select , adding distinct (found here ) and select only Origin, Stock and calculation for Score. For example, if the score is average for all of them, it will be AVG(Score) , where Score will be what you selected in the original query. If you want to use only a small subset of the elements that have the same Origin and Stock to calculate Score, I would use a subquery, matching the Origin and Stick identifiers, in the selection, so you have:

 Select Origin, Stock, (select calculation(Score) from tables where tables.stock_id = .... tables.origin_id = .....) From.... 

Hope this helps.