gistfile1.txt

SELECT locations.name, SUM(items.count_on_hand) AS stock_count, SUM(items.count_on_hand * prices.amount) AS total_price
FROM spree_stock_locations AS locations 
INNER JOIN spree_stock_items AS items 
	ON locations.id = items.stock_location_id
INNER JOIN spree_variants AS variants
	ON variants.id = items.variant_id
INNER JOIN spree_prices AS prices
	ON prices.variant_id = variants.id
WHERE prices.currency  = 'SGD'
GROUP BY (locations.id)
ORDER BY name;