A

Anonymous

Answered

query sum issue

Forum|Forum|19 years ago
April 15, 2007
14 replies
3044 views

Hi i have this query below but on my <CFDUMP VAR=#getUser#> i get massive numbers for SMSBought, any ideas what i need to do?

<CFQUERY datasource="#application.ds#" Name="GetUser">
SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password,
SUM(SR.Cost) AS SMSCost, SUM(PTS.SMS_Amount) AS SMSBought
FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS

WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
GROUP BY PTS.ClubID, CT.ClubID, SR.ClubID
</cfquery>

Advanced techniques

This topic has been closed for replies.

Correct answer BKBK

ok yes but, my cfoutput is a query to display

clubname, smsbought, smscost
clubname, smsbought, smscost
clubname, smsbought, smscost

so if i have the querys seperate, how can i loop through each club and show smsbought and smscost next to them?

One can think immediately of a query of queries

<cfquery name="q1" datasource="dsn">
select CT.ClubName as club, PTS.clubid as clubID, SUM(PTS.SMS_Amount) AS SMSBought
from SMS_Clubs_Table CT, payment_table PTS
where CT.clubid=PTS.clubid
group by PTS.clubid
</cfquery>

<cfquery name="q2" datasource="dsn">
select clubID, SUM(SR.Cost) AS SMSCost
from SMS_Records SR
group by clubid
</cfquery>

<cfquery name="q" dbtype="query">
select q1.club, q1.SMSBought, q2.SMSCost
from q1, q2
where q1.clubID=q2.clubID
</cfquery>
<cfdump var="#q#">

I am assuming that clubID is a primary key in the clubs table. I have avoided a query of a query involving 3 queries. I remember vaguely, but am not too sure, that the allowed limit is two.

I hope this works, so that you can get going. There can always be a much more efficient solution than this, of course. I am passing the baton.

BKBK

Community Expert

You could see that smsleft is an array. We have advised you to replace the keyword "left", but you haven't. All you had to do is simply replace "left" by, say, "elft". That is,

<cfset addCol = queryAddColumn(q,"elft","double",SMSLeft)>
<cfif isdefined ("URL.leftVar")>ORDER BY elft</cfif>

BKBK

Community Expert

Azadi:
"LEFT" is a reserved word

Thanks for spotting it. JohnGree could of course replace every occurrence of left with a non-reserved word.

A

Anonymous

ok i have changed the word left to smsleft, but i still get the same error,

Complex object types cannot be converted to simple values.
The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\SchoolsAdmin.cfm: line 146

144 : <td class="SMALLWhite"><div align="left">#Cost#</div></td>
145 : <td class="SMALLWhite">
146 : <div align="left">#smsleft#</div></td>

<cfquery name="q1" datasource="#application.ds#">
select CT.ClubName as club, PTS.clubid as clubID, SUM(PTS.SMS_Amount) AS SMSBought
from SMS_Clubs_Table CT, payment_table PTS
where CT.clubid=PTS.clubid AND School = 1
group by PTS.clubid
</cfquery>

<cfquery name="q2" datasource="#application.ds#">
select clubID, SUM(SR.Cost) AS SMSCost
from SMS_Records SR
group by clubid
</cfquery>

<cfquery name="q" dbtype="query">
select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost, q2.ClubID
from q1, q2
where q1.clubID=q2.clubID
</cfquery>

<cfset SMSLeft=arraynew(1)>
<cfloop query="q" >
<cfset SMSLeft[currentRow]=bought-cost>
</cfloop>
<cfset addCol = queryAddColumn(q,"left","double",SMSLeft)>

<cfquery name="GetUser" dbtype="query">
select *
from q
<cfif isdefined ("URL.School")>ORDER BY club</cfif>
<cfif isdefined ("URL.Used")>ORDER BY cost</cfif>
<cfif isdefined ("URL.smsLeft")>ORDER BY smsleft</cfif>
</cfquery>

BKBK

Community Expert

ok so what is the actual query i need to order by smsleft?
In your code of April 30, just change the line

select q1.club, q1.SMSBought, q2.SMSCost, q2.clubID

into

select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost

and see what happens.

A

Anonymous

ok i have changed the aliases but i still get th same error which is

Error Executing Database Query.

Query Of Queries syntax error.
Encountered "left. Incorrect ORDER BY column reference

.
Only simple column reference, alias name, and integer column id are allowed.
Example: You can use alias to refer to a complex expression:
SELECT (a+b)/2 as x FROM T ORDER BY x

The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\SchoolsAdmin.cfm: line 52

50 : <cfif isdefined ("URL.School")>ORDER BY club</cfif>
51 : <cfif isdefined ("URL.Used")>ORDER BY cost</cfif>
52 : <cfif isdefined ("URL.Left")>ORDER BY left</cfif>
53 : </cfquery>
54 :

BKBK

Community Expert

BKBK:
> I wrote
> select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost

JohnGree:
Hi i dont understand what you mean?

My query defined the aliases club, bought and cost. Yours didn't.

A

Anonymous

ok so what is the actual query i need to order by smsleft?

BKBK

Community Expert

ok thanks, i really need it in a query so i can do a sort by smsleft
i tried you way but i get this error, any ideas what i need to do?

You haven't quite done it the way I suggest. Then there would be cost (not SMSCost), bought and club. In any case, there was some confusion. The name SMSLeft stands for a column and for an array. That was not the intention. Use this instead

<cfset addCol = queryAddColumn(q,"left","double",SMSLeft)>

A

Anonymous

ok thanks that displays ok now.

how do i now put the order by clause in place?

i have a link that adds the word left to the url, i have <cfif isdefined ("URL.Left")>ORDER BY left</cfif> but this wwouldnt work

how else can i do it?

<cfquery name="GetUser" dbtype="query">
select q1.club, q1.SMSBought, q2.SMSCost, q2.clubID
from q1, q2
where q1.clubID=q2.clubID
<cfif isdefined ("URL.School")>ORDER BY q1.club</cfif>
<cfif isdefined ("URL.Used")>ORDER BY q2.SMSCost</cfif>
<cfif isdefined ("URL.Left")>ORDER BY left</cfif>

</cfquery>

<cfset SMSLeft=arraynew(1)>
<cfloop query="GetUser" >
<cfset SMSLeft[currentRow]=SMSbought-SMScost>
</cfloop>
<cfset addCol = queryAddColumn(GetUser,"left","double",SMSLeft)>

BKBK

Community Expert

A quick test to see what's going on. Create the 3 tables t1, t2, t3. Each has 2 columns. One column is id, of type int, the other is txt, of type varchar.

Insert the following 6 rows:

table t1
=======
id=1, txt =x

table t2
=======
id=1, txt=y1
id=1, txt=y2

table t3
=======
id=1, txt=z1
id=1, txt=z2
id=1, txt=z3

Run the code

<cfquery name="q" datasource="dsn">
select sum(t1.id) as s
from t1, t2, t3
where t1.id=t2.id and t1.id=t3.id and t2.id=t3.id
group by t1.id
</cfquery>
<cfdump var="#q#">

The value of s will be 6, not 1.

A

Anonymous

ok yes i understand what the problem is, how would i create the query using unions?

would applying a union stop the problem? if so how would i use unions in my query?

BKBK

Community Expert

In my opinion, doing a union will take you away from your original logic, that of joins. A union requires that the concatenated select-statements have the same structure. They must have the same number of columns. Corresponding columns must have the same data type or data types that can be converted to each other. The order of the columns in each SELECT statement matters, too. The result will unlikely be what you wanted to get with joins.

What comes to mind is that you first leave out the aggregates, because the ones you get with a join are different from the ones you need. Get the other information you need

SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password
FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID

and get your aggregates as before

select SUM(PTS.SMS_Amount) AS SMSBought
from payment_table PTS
group by clubid

select SUM(SR.Cost) AS SMSCost
from SMS_Records SR
group by clubid

It's all up to you. If you're not satisfied with the information as it stands, you can then proceed with a union or with a query of a query.

BKBK

Community Expert

Efecto747
> For example, if a record in SMS_Clubs_Table had 3 related records in the
> SMS_Records table and 3 related records in payment_table then you'd get
> 9 rows back. Any aggregate functions - SUM() COUNT() etc. - would be incorrect.

My thoughts, too. For example, clubID is possibly not a unique foreign key in one or more of the tables.

My last query is meant to show that. It is the same as the query that returns the expected answer, with the exception of the join statements.

E

efecto747

Participating Frequently

Your query is creating two inner joins on the SMS_Clubs_Table to two tables which have a many to one relationship with it. This will give you more rows than you're expecting as it returns a row for each matching record in SMS_Records and each matching record in payment_table - this is why your totals are out.

For example, if a record in SMS_Clubs_Table had 3 related records in the SMS_Records table and 3 related records in payment_table then you'd get 9 rows back. Any aggregate functions - SUM() COUNT() etc. - would be incorrect.

Try breaking it into two queries or make it into a UNION query.

cheers.

BKBK

Community Expert

that still gives the same bogus numbers?
Even the first query? OK, just to corner the gremlin, what about

<CFQUERY datasource="#application.ds#" Name="GetUser">
SELECT SUM(PTS.SMS_Amount) AS SMSBought
FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
GROUP BY PTS.ClubID
</cfquery>

BKBK

Community Expert

P.S.: I wondered whether the group by was having more of an effect on SUM(SR.Cost) instead

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded