sohal07 sohal07 - 11 months ago 50
PHP Question

Need help to create mysql query for multiple selects for specific conditions

I need some help to make a SQL query.

I have table 'stock_exchange' which records the stock interchanged between four different stores/sites.

It records

date, item_id, qty, price, total_price, from_site, to_site

Now I need to create a query to run for one of the stores/site which will return total price for the stock sent and received between other three stores.

For example: there are Store1, Store2, Store3, Store4

If I need to check for Store1, it should return:

Store Name____Total Sent____Total Received


I tried myself, but was only able to get sum of either stock sent or received.

Here is the fiddle

Answer Source

Here is a query which shows the sent and received amounts from Cafe Rosewood to all other stores:

SELECT GREATEST(, AS from_store,
       LEAST(, AS to_store,
       SUM(CASE WHEN < THEN t1.total_price ELSE 0 END) AS total_sent,
       SUM(CASE WHEN > 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
HAVING MAX(GREATEST(, = '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.