CODEWITHSUNDEEP
mysqldatabase-design
Steven Sokulski
Steven Sokulski

Reputation: 374

SUM() for multiple values in related tables

I have three relevant tables in a MySQL database:

Inventory_transactions have a quantity_offset that stores how many of a given inventory_item were added or removed from a given location.

I have the following query, which will get the global_quantity (sum of all quantity_offsets for a given inventory_item_id) as well as location_quantity which does the same for a specific location so long as I know the location ID.

SELECT sku, name, SUM(quantity_offset) AS global_quantity,
(
SELECT SUM(quantity_offset)
    FROM inventory_transactions
    WHERE inventory_items.inventory_item_id = inventory_transactions.inventory_item_id AND inventory_transactions.location_id = 1
) AS location_quantity
FROM inventory_items
JOIN inventory_transactions ON inventory_items.inventory_item_id = inventory_transactions.inventory_item_id
GROUP BY sku

What I'd like to end up with is a column being added for each location, so the result may have values as such:

sku, name, global_quantity, denver_quantity, dallas_quantity, ft_wayne_quantity

My fallback is to do this on an individual inventory_item page (when I'll know the inventory item and can group by location) but being able to do it in a single query and put it all into one table would be pretty great.

Initially, I had thought I could iterate through the subqueries but was unable to set the column name (in AS) programmatically.

Thank you in advance for any assistance you can provide.

Upvotes: 0

Views: 138

Answers (3)

Zoltan Fedor
Zoltan Fedor

Reputation: 2097

You should be using conditional sums - this is the most efficient way (fastest query) I know of:

SELECT sku, name, SUM(quantity_offset) AS global_quantity,
  sum(case when inventory_transactions.location_id = 1 then quantity_offset else 0 end) as  loc1_quantitity,
  sum(case when inventory_transactions.location_id = 2 then quantity_offset else 0 end) as loc2_quantitity,
  sum(case when inventory_transactions.location_id = 3 then quantity_offset else 0 end) as loc3_quantitity
FROM inventory_items JOIN inventory_transactions ON inventory_items.inventory_item_id = inventory_transactions.inventory_item_id
GROUP BY sku

Upvotes: 1

spencer7593
spencer7593

Reputation: 108420

As an aside, rather than using a subquery in the SELECT list, you could get the same result with a SUM aggregate on an expression that conditionally returns the quantity_offset if the row is from a given location. I would also use a LEFT outer join to the inventory_transactions table, so that I would be sure to get a row back for each row in inventory_items:

SELECT i.sku
     , i.name
     , SUM(t.quantity_offset) AS global_quantity
     , SUM(IF(t.location_id = 1,t.quantity_offset,NULL)) AS location_1_quantity 
  FROM inventory_items i
  LEFT
  JOIN inventory_transactions t ON i.inventory_item_id = t.inventory_item_id
  GROUP BY i.sku

(That doesn't answer your question, but I wanted to make you aware of that.)

To answer your question, firstly, it's not possible for a SELECT statement to dynamically change the number or types of the columns it returns in a resultset. You need to define an expression for every column you want returned. (The generation of the SQL SELECT statment text could be done dynamically, to produce a particular SELECT statement, but the columns returned by that SELECT statement are fixed by the SELECT statement.)

If I had a relatively fixed set of location_id values, and I needed the result set you specify, I would repeat the SUM of the conditional expression multiple times, once for each location or whatever set of locations I wanted:

SELECT i.sku
     , i.name
     , SUM(t.quantity_offset) AS global_quantity
     , SUM(IF(t.location_id = 1,t.quantity_offset,NULL)) AS location_1_quantity 
     , SUM(IF(t.location_id = 2,t.quantity_offset,NULL)) AS location_2_quantity 
     , SUM(IF(t.location_id = 3,t.quantity_offset,NULL)) AS location_3_quantity 
     , SUM(IF(t.location_id IN (1,2,3),t.quantity_offset,NULL)) AS location_123_quantity 
     , SUM(IF(t.location_id = 4,t.quantity_offset,NULL)) AS location_4_quantity
  FROM inventory_items i
  LEFT
  JOIN inventory_transactions t ON i.inventory_item_id = t.inventory_item_id
  GROUP BY i.sku

If the set of columns to be returned needs to be truly dynamic, then I wouldn't return them as columns at all. Instead, I would return the SUM(quantity_offset) for each location as a separate row, and then handle the conversion to a column-wise representation on the client side.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

try this

   SELECT sku, name, SUM(quantity_offset) AS global_quantity,
       (
        SELECT SUM(quantity_offset)
         FROM inventory_transactions
           WHERE inventory_items.inventory_item_id = inventory_transactions.inventory_item_id AND inventory_transactions.location_id = 1
          AND location = denver) AS denver_quantity ,
        (
        SELECT SUM(quantity_offset)
         FROM inventory_transactions
           WHERE inventory_items.inventory_item_id = inventory_transactions.inventory_item_id AND inventory_transactions.location_id = 1
          AND location = dallas) AS dallas_quantity,
         (
        SELECT SUM(quantity_offset)
         FROM inventory_transactions
           WHERE inventory_items.inventory_item_id = inventory_transactions.inventory_item_id AND inventory_transactions.location_id = 1
          AND location = ft_wayne) AS ft_wayne_quantity
   FROM inventory_items
   JOIN inventory_transactions ON inventory_items.inventory_item_id = inventory_transactions.inventory_item_id
   GROUP BY sku

note that i used location as column name i dont know what column name of location you have and just replace it

Upvotes: 0

Related Questions