Alex Fields Alex Fields - 6 months ago 150
SQL Question

Supply chain SQL query

I am having issues with my SQL code.

I want to display the total count of stores that correspond with a certain Distribution Center/ WH. I want both to be tied to a certain item.

For example: I have one WH that gave a certain item to 50 stores. I want the query to tell me if I give it the item# and WH# it will give me the amount of stores that received that item.

SELECT
COUNT(*) AS TOTAL_STORES
FROM
(SELECT
a.WH_i, a.STORE_i
FROM
WH a, STORES b
WHERE
a.item_i = 2201
AND a.WH_i IN (10)
GROUP BY
a.WH_i, B.STORE_i
HAVING
COUNT(a.item_i) = 1) a;


Table WH has the warehouse numbers and item numbers and store has the store numbers.

I am new to SQL so I am not 100% confident with joins just yet. Any help is greatly appreciated though!

EDIT: I tried joining the two tables without actually using the JOIN clause and it is still not giving proper results.

SELECT COUNT(*) AS TOTAL_STORES
FROM(
SELECT a.WH_i, b.STORE_i
FROM WH a, STORES b
WHERE a.item_i = b.sku_i
AND a.stock_i = b.stock_i
AND a.item_i = 2201
AND a.WH_i IN (10)
GROUP BY a.WH_i, B.STORE_i
HAVING COUNT(a.item_i) = 1
)a;

Answer Source

You only want the count of stores so just pull stores in your subquery.

SELECT COUNT(STORE_i) AS TOTAL_STORES
FROM( 
  SELECT b.STORE_i
  FROM  WH a
      JOIN STORES b
      ON a.item_i = b.sku_i
      AND a.stock_i = b.stock_i
  WHERE 
      a.item_i = 2201
      AND a.WH_i = 10
  GROUP BY b.STORE_i
  HAVING COUNT(a.item_i) = 1
 ) a; 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download