StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is the query I've been working on. However, the results are slighly different from the ones you've posted in your question:</p> <pre><code>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 </code></pre> <p>Result:</p> <pre> +-------------+-------------+------------+ | ORIGIN_NAME | STOCK_TITLE | FINALSCORE | +-------------+-------------+------------+ | US | P1 | 93 | | CA | P3 | 90 | | MX | Q4 | 100 | | MX | B3 | 100 | +-------------+-------------+------------+ </pre> <p>Let me know if this did the trick.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload