Having issues querying a query

Cold Fusion 5

OK, I'm creating a report with a few different sections which require data to be compiled differently.  I have one main query that retrieves data using a stored procedure, then a few queries of that query to order the data as needed.  For some reason I'm having issues with adding WHERE clauses or ORDER BY clauses to a couple of the sub-queries.

Main Query:
      <cfquery name="qryMainSource" datasource="theDB">
            EXECUTE
                  sm_spGetDASSImpressions
                        @in__dt_StartDate      = '#variables.StartDate#',
                        @in__dt_EndDate            = '#variables.EndDate#',
                        @in__int_BrandID      = #variables.BrandID#,
                        @in__int_ClusterID  = #variables.ClusterID#
      </cfquery>


Sub Queries I'm having Issues With:

      <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
            SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
            FROM qryMainSource
            WHERE TotalWebSite > -1
            GROUP BY CustomerClassificationName
            ORDER BY theSum DESC
      </cfquery>
First issue I'm having is that the WHERE clause is causes the error: "Invalid SQL" to be thrown.  I thought maybe this has something to do with the aggregate function being used here so I created an addition sub query to work around this.

Additional Sub Query:
      <cfquery name="qrySubCountsRoot" DBTYPE="query">
            SELECT CustomerClassificationName, TotalWebSite
            FROM qryDASS_Source
            WHERE TotalWebSite > -1
      </cfquery>

Then modified the first sub-query to use this as the base data:
      <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
            SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
            FROM qrySubCountsRoot
            GROUP BY CustomerClassificationName
            ORDER BY theSum
      </cfquery>

Now this throws the error:
THESUM is not a column that can be sorted on.

Now I know that TotalWebSite can be summed and ordered by because I do it with some of my other sub-queries that do not require a where clause (where totalwebsite > -1).  What the heck is going on here????
scottd78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pinaldaveCommented:
Hi scottd78,
   SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite)
 as theSum
           FROM qryMainSource
           GROUP BY CustomerClassificationName
           ORDER BY theSum DESC
           HAVING TotalWebSite > -1

try this.



Regards,
---Pinal
0
kkhippleCommented:
    <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
          SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
          FROM qryMainSource
          WHERE TotalWebSite > -1
          GROUP BY CustomerClassificationName
          ORDER BY theSum DESC
     </cfquery>


aggregate functions should always be listed first.. try this instead

     <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
          SELECT Count(*) as theCount, Sum(TotalWebSite) as theSum, CustomerClassificationName
          FROM qryMainSource
          WHERE TotalWebSite > -1
          GROUP BY CustomerClassificationName
          ORDER BY theSum DESC
     </cfquery>



    <cfquery name="qrySubCountsRoot" DBTYPE="query">
          SELECT CustomerClassificationName, TotalWebSite
          FROM qryDASS_Source
          WHERE TotalWebSite > -1
     </cfquery>

is TotalWebSite a varchar or int datatype?   if its a varchar datatype then try this

    <cfquery name="qrySubCountsRoot" DBTYPE="query">
          SELECT CustomerClassificationName, TotalWebSite
          FROM qryDASS_Source
          WHERE TotalWebSite > '-1'
     </cfquery>




why you doing a query on a query if you have WHERE TotalWebSite > -1 specified in both
0
kkhippleCommented:
wouldn't it be:

   SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
           FROM qryMainSource
           GROUP BY CustomerClassificationName
           ORDER BY theSum DESC
           HAVING thesum > -1
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pinaldaveCommented:
Hi kkhipple,
i think that is not valid. you can do having on original column not the created one. I may be wrong though. I still think, my query is correct.

Regards,
---Pinal
0
scottd78Author Commented:
Wow, thanks for all the quick responses.  I'll address them in order.

1)  CF appears to ignore the HAVING clause listed after the ORDER BY clause, but no longer causes an error.  It's also not eliminating the records that aren't > -1.
2)  Reordering the select list did not help.
3)  TotalWebSite is of type integer.
4)  Their are other sub-queries that do not require the TotalWebSite to be > -1.  This particluar data set should not include those records.
5)  I'm not interested in putting a clause on the Sum of TotalWebSite rather eliminating the records which aren't > -1 before summing the records.

Thanks again!
0
mrichmonCommented:
HAVING should always come before an ORDER BY so if that solution will work it would be:


 SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
          FROM qryMainSource
          GROUP BY CustomerClassificationName
          HAVING TotalWebSite > -1
          ORDER BY theSum DESC
0
aftershoxCommented:
This is a wild one... but I've had problems when using QOQ SQL functions when one of the columns contained a null value. Try cfdumping your query structures just to make sure the data is what you expected. To get around that the original query needs to convert nulls to a space (or perhaps a number in your case).
0
Jerry_PangCommented:

Hi,

what database are you using?
what do you mean subqueries? do you have a main query?
-see, you have the storedprocedure

>>then a few queries of that query to order the data as needed.
how do you do this?

is this a standalone query that does not get any values from the main query?
i tried running you sql statement on MsSQL server, and it is running ok.

need more info, i guess
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pinaldaveCommented:
split between all the experts as I think everybody has contributed to reach to the answer.
Though, asker has not reponded I believe the answer is correct.
0
scottd78Author Commented:
never found a solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.