-
1. Re: MYSQL Data Structure
Tom WJun 13, 2016 6:44 AM (in response to Christopher Demundo)
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
FROM ProductSales
GROUP BY Product
UNION ALL
SELECT Product, null as TotalSales, SUM(OpenOrders) as OpenOrders
FROM ProductOpenOrders
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
-
2. Re: MYSQL Data Structure
Christopher Demundo Jun 13, 2016 6:50 AM (in response to Tom W)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.
-
3. Re: MYSQL Data Structure
Tom WJun 13, 2016 7:02 AM (in response to Christopher Demundo)
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
FROM Sales
UNION ALL
SELECT Product, null as SaleID, OrderNumber, SaleAmount, 'Open' as OrderStatus
FROM OpenOrders
-
-
5. Re: MYSQL Data Structure
Tom WJun 13, 2016 7:34 AM (in response to Christopher Demundo)
null as is the best in my experience.
Create a view, do it once and you'll never have to worry about it again.