MySQL Union All - value priority

Question

I have the following query that UNION two tables and retrieves the SUM of some fields and other information as you can see:

SELECT SUM(a.quantity_input) AS `quantity_input`, 
       SUM(a.quantity_output) AS `quantity_output`,  
       a.average_cost_price, 
       a.item_reference, 
       a.item_description
FROM   ((SELECT SUM(aco.quantity_input)     AS `quantity_input`, 
                SUM(aco.quantity_output)    AS `quantity_output`, 
                aco.average_cost_price, 
                item.reference              AS `item_reference`, 
                item.description            AS `item_description`
         FROM   stock_accumulated AS aco 
         INNER JOIN items AS item ON item.id = aco.item_id
         WHERE aco.year = '2016' AND 
               aco.month < '03'
         GROUP  BY item.reference) 

        UNION ALL 

        (SELECT Sum(mov.quantity_input)    AS `input_quantity`, 
                Sum(mov.quantity_output)   AS `quantity_output`, 
                mov.average_cost_price, 
                item.reference             AS `item_reference`, 
                item.description           AS `item_description`
         FROM   stock_movements AS mov 
         INNER JOIN items AS item ON item.id = mov.item_id 
         WHERE mov.movement_date <= '2016-03-31' AND 
               mov.movement_date >= '2016-03-01'
         GROUP  BY item.reference)) a 
GROUP  BY a.item_reference

My only problem (at least so far) with this query is that I need to retrieve the average_cost_price of the last row in my second table.

You can try the following query in SQLFiddle and see the image below to understand what I'm referring to.

If you want to retrieve the "last" average cost price, you'll need a (potentially complicated) subquery to retrieve it... and to define what you mean by "last" — Uueerdo, Apr 28 '16 at 17:56
Eching Uueerdo's question: *which* row from \`mov\` is the "last" row for a given value of \`item_reference\` ... the one with the largest value of \`movement_date\`? What if there are multiple rows with the same latest \`movement_date\`? And different values of \`item\`.\`id\`? Which of those rows is the "last" one? — spencer7593, Apr 28 '16 at 18:37
@spencer7593, my SQLFiddle is wrong in the ID's..obviously it's auto-incremented so its (1, 2, 3) and not (1, 1, 1). I want to retrieve the last ID, in this case it would be the line 3 with value 37.30. Please see the new SQLFiddle http://sqlfiddle.com/#!9/54ef0/1 — Linesofcode, Apr 29 '16 at 08:13

score 0 · Answer 1 · answered Apr 28 '16 at 17:42

0

Have you tried "tightening" up your Where statement with the date? It could be pulling the first occurence. I would recommend testing

WHERE mov.movement_date <= '2016-03-31' AND 
               mov.movement_date >= '2016-03-01'

as

WHERE mov.movement_date <= '2016-03-31' AND 
               mov.movement_date >= '2016-03-27'

Then you can mark that off if it doesnt work. Lemme know ill try to help you.

answered Apr 28 '16 at 17:42

Max Uland

87
10

I cant change the `movement_date`. I had a few problems in my previous SQLFiddle, please see the new one. http://sqlfiddle.com/#!9/54ef0/1 – Linesofcode Apr 29 '16 at 08:14

score 0 · Accepted Answer · edited May 23 '17 at 10:28

Solved.

I had to use group_concat function that provides the ability to order a field.

group_concat(mov.average_cost_price ORDER BY mov.id DESC SEPARATOR ",")

But this function returns all the values, and since I can't use the LIMIT function I used the substring_index function to return me only one value.

substring_index(group_concat(mov.average_cost_price ORDER BY mov.id DESC SEPARATOR ","), ",", 1) as `average_cost_price`

GROUP_CONCAT with limit

MySQL Union All - value priority

2 Answers2