0

I'm having a hard time figuring out how to do this, and I can't explain it well. Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it out and ask for assistance. Here are the details:

Columns for the example: My original SQL query created this table (Data specific to this problem comes from one HUGE table)

Sample code:

SELECT 
inventory .01 as  `Item#`, 
inventory .02 as `warehouse`, 
inventory .03 as `qty`, 
inventory .04 as `price`, 
inventory .05 as `weight`, 
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5

Returns this:

Item# | warehouse | qty | price | weight | other
1           1        1      3        4       5
1           2        6      3        4       5
1           3        3      3        4       5

I've re-written the table using group by to make the data look like this:

SELECT 
inventory .01 as  `Item#`, 
inventory .03 as `qty (whse1)`, 
inventory .03 as `qty (whse2)`, 
inventory .03 as `qty (whse3)`, 
inventory .04 as `price`, 
inventory .05 as `weight`, 
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5
GROUP BY
inventory .01

Returns This:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            1             1           3        4       5

Here’s where I’m stuck. I don’t know how to tell write the SQL code to make the 3 warehouse fields to show like this:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            6             3           3        4       5

I’m sure this is easy to accomplish. I just can’t figure it out.

My DB contains 30K rows, and each item is included 10 times (once per warehouse.) I want to be able to see 3K rows instead, based on the item no, with the qty per warehouse. This is just a sample of the data, the real tables and fields are clearly labeled. This is just a sample of the issue I'm dealing with.

The price, weight and other are all the same for all the items. The only real difference for these parts is the qty per warehouse.


Adding to give real code example. The responses I've received so far seem to come from creating a table from scratch vs. selecting data from an existing table. I've tried implementing the example and I'm getting errors. I can attach a screenshot of some of the data to give you an example of the content included in the database.

SELECT
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
COALESCE(inventory.ONHAND,0) AS `On Hand (LA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (SF)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (HP)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (CHINA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (INDO)`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (LA)` 
AND inventory.WHSE=00)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (SF)` 
AND inventory.WHSE=10)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (HP)` 
AND inventory.WHSE=20)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (CHINA)
` AND inventory.WHSE=50)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (INDO)
` AND inventory.WHSE=70)
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD
0

1 Answer 1

5

Given this test data: (this is a hint on how to make a more readable question in the future):

create table inventory(
  id int unsigned primary key auto_increment,
  item int unsigned,
  warehouse int unsigned,
  qty int unsigned,
  price int unsigned,
  weight int unsigned,
  other int unsigned);

insert into inventory(item, warehouse, qty, price, weight, other) values
  (1,1,1,3,4,5),
  (1,2,6,3,4,5),
  (1,3,3,3,4,5);

> select item,warehouse,qty,price,weight,other from inventory;
+------+-----------+------+-------+--------+-------+
| item | warehouse | qty  | price | weight | other |
+------+-----------+------+-------+--------+-------+
|    1 |         1 |    1 |     3 |      4 |     5 |
|    1 |         2 |    6 |     3 |      4 |     5 |
|    1 |         3 |    3 |     3 |      4 |     5 |
+------+-----------+------+-------+--------+-------+

You can have the desired output with the following query:

> select distinct
  i.item,
  COALESCE(w1.qty,0) as w1,
  COALESCE(w2.qty,0) as w2,
  COALESCE(w3.qty,0) as w3,
  i.price,
  i.weight,
  i.other
from inventory i
  left join inventory w1 on (i.item = w1.item AND w1.warehouse=1)
  left join inventory w2 on (i.item = w2.item and w2.warehouse=2)
  left join inventory w3 on (i.item = w3.item and w3.warehouse=3);

+------+------+------+------+-------+--------+-------+
| item | w1   | w2   | w3   | price | weight | other |
+------+------+------+------+-------+--------+-------+
|    1 |    1 |    6 |    3 |     3 |      4 |     5 |
+------+------+------+------+-------+--------+-------+

COALESCE function and LEFT JOINs are for the case that there is no row for item, warehouse combination

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.