5 Replies Latest reply on Jun 13, 2016 7:34 AM by Tom W

    MYSQL Data Structure

    Christopher Demundo
    Christopher Demundo Jun 13, 2016 6:26 AM

      I have one table that is invoice level sales data.  Each line in the database represents a  final transaction.

       

      I have another table where each line represent a sales transaction that has not been paid for (open orders). 

       

      For reporting purposes, I need to be able to combine the two across a product name field:

       

      Product NameTotal SalesOpen Orders
      Product A500,000350,000

       

      Currently, I am importing total sales as a mysql database and exporting open orders to a text file, and then using data blending across product name. 

       

      I would like a better solution that doesn't require me to create a text file for open orders.  I can't think of a way to do this all in MYSQL (since each table represents individual level transactions, I can't join the two in sql).  

       

      Tableau, as far as I can tell, doesn't let me pull from two MYSQL databases at one time, which is why I started using the text file.

       

      Any solutions?  Currently I have the invoice level sales data automatically updating every morning.  I have to go through a manual step of updating the text file for the open order data that I am trying to eliminate.

        • 1. Re: MYSQL Data Structure
          Tom W
          Tom W Ambassador Jun 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
            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 W
              Tom W Ambassador Jun 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

              • 4. Re: MYSQL Data Structure
                Christopher Demundo
                Christopher Demundo Jun 13, 2016 7:32 AM (in response to Tom W)

                Thanks Tom. 

                 

                I have a pretty big disparity in column equivalency.  Is the best way to approach using UNION just to use "null as"?  Is there a better solution given how dissimilar the tables are? 

                 

                • 5. Re: MYSQL Data Structure
                  Tom W
                  Tom W Ambassador Jun 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.