query sum issue

[Guest]

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>

[Dan_Bracuk]

What does this give you?
select max(sms_amount)
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

[Guest]

Hi i have tried the group clause and still i get the same result

the max output was 2375

i also did this

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

which shows the results fine, so the problem comes when i add the two tables SMS_Clubs_Table and SMS_Records

any ideas why this would be hapening

[paross1]

Shouldn't your group by actually look like this instead?

GROUP BY CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password

PTS.ClubID, CT.ClubID, and SR.ClubID would all be the same value and you are actually only selecting CT.ClubID in your SELECT.

Phil

[Dan_Bracuk]

I have an idea why it would be happening.

to make a long story short, add this to your where clause
and sr.clubID = sms.clubID

[Guest]

ok i have tried that but still the same, also what is sms.clubid?

[BKBK]

What do these give us?

<CFQUERY datasource="#application.ds#" Name="GetUser">
SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password, 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>

<CFQUERY datasource="#application.ds#" Name="GetUser">
SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password, SUM(PTS.SMS_Amount) AS SMSBought, SUM(SR.Cost) AS SMSCost
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>

[Guest]

no that still gives the same bogus numbers?

i dont understand, when i do the query on it own i get the correct results

[BKBK]

GROUP BY PTS.ClubID

[BKBK]

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

[BKBK]

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>

[efecto747]

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]

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.

[BKBK]

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.

[Guest]

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]

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.

[Guest]

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?

[BKBK · Accepted solution]

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.

[Guest]

ok thanks, that works, i just need to do 1 more thing to have a order by

do a sum of

q1.SMSBought minus q2.SMSCost to give me SMSLeft, but how can i do this in a QofQ ?


<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 SMSLeft</cfif>

</cfquery>

[BKBK]

q1.SMSBought minus q2.SMSCost to give me SMSLeft, but how can i do this

Introduce aliases in the QoQ, thus

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

then simply do <cfoutput query="q">#bought - cost#<br></cfoutput>

If you insist on having it in a query, one possibility is:
<cfset SMSLeft=arraynew(1)>
<cfloop query="q" >
<cfset SMSLeft[currentRow]=bought-cost>
</cfloop>
<cfset addCol = queryAddColumn(q,"SMSLeft","double",SMSLeft)>
<cfdump var="#q#">

[Guest]

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?

Complex object types cannot be converted to simple values.
The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a <CFIF> tag. This was possible in ColdFusion 2.0 but creates an error in later versions.


The error occurred in httpdocs\SchoolsAdmin.cfm: line 139

137 : <td class="SMALLWhite"><div align="left">#SMSCost#</div></td>
138 : <td class="SMALLWhite">
139 : <div align="left">#smsleft#</div></td>

[BKBK]

You could just extend my suggestion of April 21 with

[Guest]

ok thanks, i have tried that but get an error, i have attached my full code

what am i doing wrong?

[Newsgroup_User]

JohnGree wrote:
> ok thanks, i have tried that but get an error, i have attached my full code
>
> what am i doing wrong?

what's the error you are getting?

> <cfloop query="q" >
> <cfset SMSLeft[currentRow]=SMSbought-SMScost>
> </cfloop>

i think you should be using cfoutput instead of cfloop in the above...

--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com

[Guest]

no that is not an output, i have this table below which when the top headers of the table are clicked it should sort the output order

the error i get 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 :




so this is my full code