Skip to main content
A
Anonymous
April 15, 2007
Answered

query sum issue

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




14 replies

Show previous replies
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 16, 2007
GROUP BY PTS.ClubID

D
Dan_Bracuk
Inspiring
April 16, 2007
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
A
Anonymous
April 16, 2007
ok i have tried that but still the same, also what is sms.clubid?
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 16, 2007
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>



P
paross1
Participating Frequently
April 15, 2007
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
D
Dan_Bracuk
Inspiring
April 15, 2007
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
A
Anonymous
April 15, 2007
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices