Professional Web Applications Themes

query of queries with avg() - Coldfusion - Getting Started

cfmx 6.1 when i use avg() in a query of queries it rounds the results (actually rounds results down)...so i'm wondering if i am doing this incorrectly. here is my example: <cfscript> q1="select * from mbr"; q2="select avg(mbrlos) as alos from mbr"; q3="select avg(mbrlos) as alos from qry1"; </cfscript> <h3>Query 1 (qry1)</h3> select * from mbr <cfquery name="qry1" datasource="qqtest"> #q1# </cfquery> <cfdump var="#qry1#" /> <h3>Query 2 (qry2)</h3> select avg(mbrlos) as alos from mbr <cfquery name="qry2" datasource="qqtest"> #q2# </cfquery> <cfdump var="#qry2#" /> <h3>Query 3 (qry3)</h3> select avg(mbrlos) as alos from qry1 <cfquery name="qry3" dbtype="query"> #q3# </cfquery> <cfdump var="#qry3#" /> output for ...

  1. #1

    Default query of queries with avg()

    cfmx 6.1
    when i use avg() in a query of queries it rounds the results (actually rounds
    results down)...so i'm wondering if i am doing this incorrectly. here is my
    example:

    <cfscript>
    q1="select * from mbr";
    q2="select avg(mbrlos) as alos from mbr";
    q3="select avg(mbrlos) as alos from qry1";
    </cfscript>
    <h3>Query 1 (qry1)</h3> select * from mbr
    <cfquery name="qry1" datasource="qqtest">
    #q1#
    </cfquery>
    <cfdump var="#qry1#" />
    <h3>Query 2 (qry2)</h3> select avg(mbrlos) as alos from mbr
    <cfquery name="qry2" datasource="qqtest">
    #q2#
    </cfquery>
    <cfdump var="#qry2#" />
    <h3>Query 3 (qry3)</h3> select avg(mbrlos) as alos from qry1
    <cfquery name="qry3" dbtype="query">
    #q3#
    </cfquery>
    <cfdump var="#qry3#" />


    output for query 1 looks like:
    query
    MBRID MBRLOS MBRNAME
    1 1 5 bob
    2 2 2 john
    3 3 1 jim
    4 4 7 sam


    output for query 2 looks like:
    query
    ALOS
    1 3.75

    output for query 3 looks like:
    query
    ALOS
    1 3



    gogl Guest

  2. #2

    Default Re: query of queries with avg()

    Not really sure since I am still new, but just as a guess could it have to do
    with you variable type. If they are stored as an INT then they will be
    averaged as an INT. And INT only does whole numbers.

    Sorry if I am not close at all.

    PeteMares Guest

  3. #3

    Default Re: query of queries with avg()

    PeteMares is correct. I don't know if there is a way to change the data type
    of the column once it is in a query object. If MBRLOS was a character data
    type, you would have gotten an error message on qry3, but not on qry2. You can
    get the decimal results from QoQ by changing the database data type to decimal
    or doing this.

    select avg(mbrlos * 1.0) as alos from qry1


    eastinq Guest

  4. #4

    Default Re: query of queries with avg()

    perfect. thank you both. i will try this out agains my real dataset and see how it goes. this looks very promising.
    cheers.
    gogl Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. 2 queries to 1 query
    By Samall in forum Macromedia ColdFusion
    Replies: 4
    Last Post: May 26th, 12:29 PM
  3. Query of Queries in 7.0
    By Funke in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 17th, 11:12 PM
  4. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  5. Query of Queries?
    By artists_envy in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 25th, 04:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139