SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast danarashad's Avatar
    Join Date
    Nov 2006
    Location
    Nebraska USA
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query of Queries

    I am trying to do a sum from a query of a query.
    My first query is.
    SELECT Student.ID,SD.Demographic_ID,dvalue,trait_id,score
    FROM SD.Student_ID
    WHERE
    Event_ID = 91
    it returns.
    ID Demo_ID Dvalue trait_id Score
    43111 1 3 198 1
    43111 2 1 198 1
    43111 3 0 198 1
    43111 4 0 198 1
    43111 5 0 198 1
    43111 6 0 198 1
    43111 8 0 198 1

    What my qoq is.
    select sum(score) as score from getsubtrait
    where trait_id=198 and demo_ID=1 and Dvalue=3
    Whats it's doing is summing the total of all so I get 7, instead of 1.
    I have it in a loop so the trait_id, demo_ID, and Dvalue changes. But it totals all instead of what I am telling it to sum in the qoq.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would you kindly show the CFQUERY for your query-of-query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast danarashad's Avatar
    Join Date
    Nov 2006
    Location
    Nebraska USA
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    main query.
    <cfquer name="getsubtrait ">
    SELECT Student.ID,SD.Demographic_ID,dvalue,trait_id,score
    FROM SD.Student_ID
    WHERE
    Event_ID = 91
    </cfquery>

    <cfquery name="secondQuery" dbtype="query">
    select sum(score) as score from getsubtrait
    where trait_id=198 and demo_ID=1 and Dvalue=3
    </cfquery>

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your q-o-q references a column that doesn't exist

    i'd've thought you'd get an error message instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    also, why the loop?

    why can't you just loop over the results of the first query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast danarashad's Avatar
    Join Date
    Nov 2006
    Location
    Nebraska USA
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query I shorten the Demographic_ID to Demo_id just for this example.
    The was the page is set up, it would be almost impossible to loop the results, of the first query. Or i could move my main query and make adjustments.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danarashad View Post
    The query I shorten the Demographic_ID to Demo_id just for this example.
    you do realize how difficult this makes it for us to help you, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast danarashad's Avatar
    Join Date
    Nov 2006
    Location
    Nebraska USA
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah it was a mistake. I thought I changed it all the way through.


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
  •