I need some help to make a SQL query.
I have table 'stock_exchange' which records the stock interchanged between four different stores/sites.
date, item_id, qty, price, total_price, from_site, to_site
Store Name____Total Sent____Total Received
Here is a query which shows the sent and received amounts from Cafe Rosewood to all other stores:
SELECT GREATEST(t2.name, t3.name) AS from_store, LEAST(t2.name, t3.name) AS to_store, SUM(CASE WHEN t2.name < t3.name THEN t1.total_price ELSE 0 END) AS total_sent, SUM(CASE WHEN t2.name > t3.name THEN t1.total_price ELSE 0 END) AS total_received FROM stock_exchange t1 INNER JOIN sites t2 ON t1.from_site = t2.alias INNER JOIN sites t3 ON t1.to_site = t3.alias GROUP BY GREATEST(t2.name, t3.name), LEAST(t2.name, t3.name) HAVING MAX(GREATEST(t2.name, t3.name)) = 'Cafe Rosewood'
If you want to see transactions between all stores, just remove the
HAVING clause. This query employs a trick of taking
GREATEST(from_site, to_site) to group together two stores.