sohal07 sohal07 - 2 months ago 7
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
Store2_________90.90_______50
Store3_________120_______100.40

Store4_________400_______200



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

Here is the fiddle

Answer

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.

SQLFiddle