7 Replies Latest reply on Nov 13, 2009 11:14 AM by Jochem van Dieten

    Combining results with a Query of Queries - NOT QUITE THERE!!!

    emartek1 Level 1

      I have included a small sample of my database, specifically the four tables I am trying to work with in the hopes that someone can steer me down the right path. Here are the four tables and the bottom is a visual desciption of what I am trying to achieve;

       

      ORDERSSALES CALLS
      IDSaleDateTerritoryManagerUserIDSaleDateTerritoryManagerIDUserID
      42601-Oct-09Mike B1011210/1/2009Mike  B25310112
      42701-Oct-09Russ  C1011510/1/2009Mike  B25410112
      43001-Oct-09Jerry W1014510/1/2009Mike  B25510112
      43201-Oct-09Ron  H1011810/1/2009Mike  B25610112
      43301-Oct-09Ron H1011810/1/2009Ron  H25710118
      10/1/2009Ron  H25810118
      PRODUCTS ORDERED10/1/2009Ron  H26010118
      OrderIDQuantityNewExistingUserID10/1/2009Russ  C26110115
      4261201011210/1/2009Mike  B26710112
      427201011510/1/2009Mike  B26810112
      4273110115
      4301010145USERS
      4301010145TerritoryManagerZoneUserID
      4321010118Mike BCentral10112
      4321010118Russ  CCentral10115
      4321110118Jerry WCentral10145
      4321110118Ron  HCentral10118
      4332110120Don  MCentral10120
      Central ZoneTtl CallsTtl OrdersTtl ItemsTtl New Items
      Mike B51121
      Russ  C115
      Jerry W12
      Ron  H3263

       

      I have tried to achieve this result in many ways to no avail. If I try to combine PRODUCTS ORDERED with ORDERS I get an erroneous count. I finally resigned myself to getting all the info I needed with separate queries and then trying to combine them with a query of queries. This worked fine until the last query of queries which timed out with no results. I am a newbie and would appreciate any constructive help with this. I am including my queries below as well;

       

      <cfquery name="qGetOrders" datasource="manna_premier">
      SELECT Count(Orders.ID) AS CountOfID,
             Orders.UserID AS Orders_UserID,
          Users.UserID AS Users_UserID,
          Users.TMName
      FROM Users INNER JOIN Orders ON Users.[UserID] = Orders.[UserID]
      GROUP BY Orders.UserID, Users.UserID, Users.TMName;
      </cfquery>

       

      <cfquery name="qGetSalesCalls" datasource="manna_premier">
      SELECT Count(Sales_Calls.ID) AS CountOfID,
             Users.UserID AS Users_UserID,
          Users.TMName,
          Sales_Calls.UserID AS Sales_Calls_UserID
      FROM Users INNER JOIN Sales_Calls ON Users.[UserID] = Sales_Calls.[UserID]
      GROUP BY Sales_Calls.UserID, Users.UserID, Users.TMName;
      </cfquery>

       

      <cfquery name="qGetProducts" datasource="manna_premier">
      SELECT Count(ProductOrders.OrderID) AS CountOfOrderID,
             Sum(ProductOrders.Quantity) AS SumOfQuantity,
          Sum(ProductOrders.NewExisting) AS SumOfNewExisting,
          ProductOrders.UserID
      FROM Orders INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID]
      GROUP BY ProductOrders.UserID;
      </cfquery>

       

      <cfquery name="qqCombOrd_Prod" dbtype="query">
      SELECT *
      FROM qGetOrders, qGetProducts
      </cfquery>

       

      <cfquery name="qqCombOrd_ProdtoSales" dbtype="query">
      SELECT *
      FROM qqCombOrd_Prod, qGetSalesCalls
      </cfquery>

       

      PLEASE HELP!!! I'm about to go scouting for bridges to leap from!

        • 1. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
          Jochem van Dieten Level 4

          I am not entirely sure about the meaning of the NewExisting column in the ProductOrders table, but how about:

           

          SELECT

            userID

            , TerritoryManager

            , (SELECT COUNT(*)

              FROM Sales_Calls

              WHERE Sales_Calls.userID = u.userID

              ) as totalCalls

            , (SELECT COUNT(*)

              FROM Orders

              WHERE Orders.userID = u.userID

              ) as totalOrders

            , (SELECT SUM(Quantity)

              FROM ProductOrders PO

              WHERE PO.userID = u.UserID

              ) as total

            , (SELECT SUM(Quantity)

              FROM  ProductOrders PO_

              WHERE PO_.userID = u.UserID

                AND NewExisting = true

              ) as totalNew

          FROM

            Users u

          WHERE

            Zone = 'Central'

           

          This doesn't deal with any date range you might want to specify yet. There are ways to do this much more efficient in some databases using CTEs. Which database are you using?

          • 2. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
            emartek1 Level 1

            The NewExisting column would contain a 1 if new criteria is met. This column would need to be added. I made a few changes to the code in an attempt to make it work but it doesn't. I am assuming that field names would have to be the same in the subquery as in the database, so that was most of the changes. Here is how it looks now...

             

             

            <cfquery name="qZVPData" datasource="manna_premier">
            SELECT UserID,
                   TerritoryManager,
                   (SELECT COUNT(*)
                       FROM Sales_Calls
                       WHERE Sales_Calls.UserID = u.UserID) as totalCalls,
                (SELECT COUNT(*)
                 FROM Orders
                 WHERE Orders.UserID = u.UserID) as totalOrders,
                (SELECT SUM(Quantity)
                 FROM ProductOrders PO
                 WHERE PO.UserID = u.UserID) as total,
                (SELECT SUM(NewExisting)
                 FROM  ProductOrders PO_
                 WHERE PO_.UserID = u.UserID) as totalNew
            FROM Users u
            WHERE UserZone = 'Central'
            </cfquery>

             

            Here is the error message I get...

             

             

             

            Error Executing Database Query.

            [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
            The error occurred in D:\Inetpub\mannapremier\zvp_report2.cfm: line 5
            3 : <cfdump var="#FORM#">
            4 : 
            5 : <cfquery name="qZVPData" datasource="manna_premier">
            6 : SELECT UserID, 
            7 :        TerritoryManager, 
            

            SQLSTATE  07002
            SQL   SELECT UserID, TerritoryManager, (SELECT COUNT(*) FROM Sales_Calls WHERE Sales_Calls.UserID = u.UserID) as totalCalls, (SELECT COUNT(*) FROM Orders WHERE Orders.UserID = u.UserID) as totalOrders, (SELECT SUM(Quantity) FROM ProductOrders PO WHERE PO.UserID = u.UserID) as total, (SELECT SUM(NewExisting) FROM ProductOrders PO_ WHERE PO_.UserID = u.UserID) as totalNew FROM Users u WHERE UserZone = 'Central'

            • 3. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
              Dan Bracuk Level 5

              You might be able to simplify that query by getting rid of the subqueries.  Something like this

               

              SELECT TerritoryManager

              , count(sc.userid) totalcalls

              , sum(po.quantity) total

              , sum(newexisting) totalnew

              , count(o.userid) totalorders

               

              from users u join salescalls sc on u.userid = sc.userid

              join orders o on u.userid = o.userid
              join productorders po on u.userid = po.userid

               

              where userzone = 'CENTRAL'

              • 4. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
                emartek1 Level 1

                My saga continues... I am finally able to get results from my query only to find they are not all I need. I need to modify the total column to sum only the quantities where the NewExisting coumn -1. I modified the query accordingly but it is throwing an error saying there are too few parameters, expected 2. I can't figure out what is wrong here. Also, how would I perform a calculation on two of the results in my query where the solution would be output with my query results. I need to divide my totalCalls result by my totalOrders result to come up with a close persentage. Can anyone assist?

                 

                 

                 

                SELECT UserID,
                       TMName,
                    UserZone,
                          (SELECT COUNT(*)
                           FROM Sales_Calls
                           WHERE Sales_Calls.UserID = u.UserID) as totalCalls,
                    (SELECT COUNT(*)
                     FROM Orders
                     WHERE Orders.UserID = u.UserID) as totalOrders,
                    (SELECT SUM(Quantity)
                     FROM ProductOrders PO
                     WHERE PO.UserID = u.UserID) AND PO.NewExisting = "1" as total,
                    (SELECT SUM(NewExisting)
                     FROM  ProductOrders PO_
                     WHERE PO_.UserID = u.UserID) as totalNew
                FROM Users u
                WHERE UserZone = 'Central'
                GROUP BY UserZone, UserID, TMName

                • 5. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
                  Jochem van Dieten Level 4

                  Instead of PO.NewExisting = "1" use PO.NewExisting = 1 (without the quotes). It is probably easier to do the calculation in CFML if you are more familiar with that, just don't foget to protect against a divide by zero.

                  • 6. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
                    emartek1 Level 1

                    Would I perform the calculations on separate sub queries or can I use the aliases already in the query? I need to divide my 'totalorders' by 'total calls'.

                    • 7. Re: Combining results with a Query of Queries - NOT QUITE THERE!!!
                      Jochem van Dieten Level 4

                      Use what is already in the query.