There's a couple of ways to do this. The way I use the most is to union the datasets;
SELECT Product, SUM(TotalSales) as TotalSales, null as OpenOrders
GROUP BY Product
SELECT Product, null as TotalSales, SUM(OpenOrders) as OpenOrders
GROUP BY Product
Another method would be to join from a consolidated product table to the sales and open orders i.e.
SELECT p.Product, s.TotalSales as TotalSales, o.OpenOrders
FROM Products p
LEFT JOIN (SELECT Product, SUM(TotalSales) as TotalSales from ProductSales Group By Product) s on p.Product = s.Product
LEFT JOIN (SELECT Product, SUM(OpenOrders) as OpenOrders from ProductOpenOrders Group By Product) o on p.Product = o.Product
Thanks Tom. I'm actually doing the SUM and other manipulation in Tableau - I need to pull in all the individual rows for both sales and open orders.
Going off your post, would it make sense to use a union statement just to create one result set that includes all of the rows of open orders and all of the sales?
There is a column I can then use that I can filter open orders on in Tableau to be able to identify them vs actual closed sales.
Without understanding exactly what you're doing and the structure, you can definitely use the UNION approach to bring in the row level info and manipulate in Tableau. Whether or not it would make sense, I think only you can answer that once you jump into the data.
I think you'd be looking at something like;
SELECT Product, SaleID, OrderNumber, SaleAmount, 'Closed' as OrderStatus
SELECT Product, null as SaleID, OrderNumber, SaleAmount, 'Open' as OrderStatus
null as is the best in my experience.
Create a view, do it once and you'll never have to worry about it again.