• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

QofQ error

Guest
May 21, 2007 May 21, 2007

Copy link to clipboard

Copied

Hi i am getting a error of

Error Executing Database Query.

Query Of Queries runtime error.
The aggregate function [SUM(expression)] cannot operate on an operand of type [VARCHAR].

i have 2 columns in mysql database which are both DECIMAL fromats

i need to have a QofQ which i need but not sure how to change the queries to stop the error

<cfquery name="GetRecords" datasource="#application.ds#">
Select DateOfSM, RCODE, DAY(DateOfSM)as month3, OurCost, ClientCostPerSMS
FROM sms_records
WHERE MONTHNAME(DateOfSM)='#session.Daily#' AND YEAR(DateOfSM)=#session.DayYear#
</cfquery>

<cfquery name="GetRecords2" dbtype="query">
Select sum(OurCost)as TotalIncome, sum(ClientCostPerSMS)as Totalcost, month3
FROM GetRecords
GROUP BY month3
</cfquery>
TOPICS
Advanced techniques

Views

645

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2007 May 21, 2007

Copy link to clipboard

Copied

Which version of CF are you running? If 7+, try using the CAST() function
to force QoQ to understand the numeric cols are actually numeric. I'm not
sure why you'd need to do this, but QoQ works in mysterious ways,
sometimes.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

how do i use the cast function to do this?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

> how do i use the cast function to do this?

Did you look up the documentation for it? There's not a great deal to it.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

ok found it thanks

but i am getting a weird result

sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged

this column has decimals as 0.10 and 0.08 etc nothing over the amount of 1

the result i am getting is 0 but this is not true. how can i do a sum of a decimal?

thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

Have you tried casting to numeric or decimal?

--
Bryan Ashcraft (remove brain to reply)
Web Application Developer
Wright Medical Technology, Inc.
------------------------------------------------------------------
Macromedia Certified Dreamweaver Developer
Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/



"JohnGree" <webforumsuser@macromedia.com> wrote in message
news:f2urog$4v0$1@forums.macromedia.com...
> ok found it thanks
>
> but i am getting a weird result
>
> sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged
>
> this column has decimals as 0.10 and 0.08 etc nothing over the amount of
> 1
>
> the result i am getting is 0 but this is not true. how can i do a sum of a
> decimal?
>
> thanks
>
>


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

LATEST
>> ok found it thanks

Sorry to seem a bit obtuse before. I figure it's more help to people to
kind of encourage them to help themselves, rather than just dish out
answers. It can be annoying at times, I know.


>> sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged
>> this column has decimals as 0.10 and 0.08 etc nothing over the amount of 1
>> the result i am getting is 0

> Have you tried casting to numeric or decimal?

Good advice.

http://en.wikipedia.org/wiki/Integer

In casting it as an integer, you're ASKING it to "lop off" the decimal
fraction part of all the numbers. Leaving... zero. For all of them.

--
Adam


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation