Skip to main content
A
Anonymous
November 7, 2009
Question

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

  • November 7, 2009
  • 2 replies
  • 1090 views

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!

This topic has been closed for replies.

2 replies

A
Anonymous
November 11, 2009

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

J
Jochem van Dieten
Inspiring
November 11, 2009

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.

A
Anonymous
November 12, 2009

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'.

J
Jochem van Dieten
Inspiring
November 8, 2009

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?

A
Anonymous
November 10, 2009

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'

D
Dan_Bracuk
Inspiring
November 10, 2009

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'

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices