SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert into table result of two quieries

    Here is what I am trying to do:

    I have two select queries that return 3 fields each. I am trying to put together those 3 fields into an insert query.

    Here is the query:

    INSERT INTO test (a,b,c,d,e,f) VALUES ((SELECT AVG(one), AVG(two), AVG(three) FROM cdr WHERE field1 = phnum)(SELECT AVG(four), AVG(five), AVG(six) FROM cdr WHERE field2 = phnum));

    This line is a part of a stored procedure loop so phnum will be dynamic. How can I get this to work? Please help. Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    INSERT INTO test (a,b,c,d,e,f) 
    VALUES (
      SELECT 
          AVG(a.one)
        , AVG(a.two)
        , AVG(a.three) 
        , AVG(b.four)
        , AVG(b.five)
        , AVG(b.six)
      FROM cdr a
      INNER JOIN cdr b
      ON a.field1 = b.field2 
      WHERE a.field1 = phnum
    );

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit: ignore this...

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    I attempted the code above (had to add an extra set of () around the select statement and I get the following error:

    Error Code : 1136
    Column count doesn't match value count at row 1


    If I try something like this:

    INSERT INTO test (a,b,c,d,e,f)
    VALUES ( 1,2,3,4,5,6 );

    It works fine. Thanks for your reply!

  5. #5
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get rid of the VALUES keyword and the extra set of parenthesis.
    Adrian Schneider - Web Developer

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by SirAdrian View Post
    Get rid of the VALUES keyword and the extra set of parenthesis.
    You're right, I guess it was too late last night
    Code MySQL:
    INSERT INTO test (a,b,c,d,e,f)
      SELECT
          AVG(a.one)
        , AVG(a.two)
        , AVG(a.three)
        , AVG(b.four)
        , AVG(b.five)
        , AVG(b.six)
      FROM cdr a
      INNER JOIN cdr b
      ON a.field1 = b.field2
      WHERE a.field1 = phnum

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    guido, i think you missed the fact that averages 1, 2, and 3 are for phnum = field1, while averages 4, 5, and 6 are for phnum = field2

    but there is nothing in the original problem statement that says that field1 is going to be equal to field2!!

    in fact, if you make them equal, you're likely getting the wrong averages

    ~especially~ if you do this in a self-join (aggregates in a self-join are sometimes blown up)



    i hate it when people use column names like a,b,c,d,e,f and field1 and field2

    rather than simplifying things for us, it actually hides information and often we go down the wrong path...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    True. I thought phnum was a value, but of course it isn't (no quotes around it).

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone for your replies. I tried the query above and it looks like its not getting me exactly what I want. Maybe if I explained my table structure and objective better. I have simplified it to explain what I am doing so it will differ from my initial post.

    Tables:

    test = table I want my averages going into.

    Code MySQL:

    test1 = table containing my numbers and scores.

    Code MySQL:
    CREATE TABLE `test1` (
      `one` int(4) DEFAULT NULL,
      `two` int(4) DEFAULT NULL,
      `field1` bigint(10) DEFAULT NULL,
      `field2` bigint(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    insert  into `test1`(`one`,`two`,`field1`,`field2`) values (100,0,111,1234),(80,0,111,1234),(0,100,1234,222),(0,70,1234,222);

    Suppose team 111 and team 222 are in my system. I have a procedure that loops through my teams. In this cause, it would be team 111 and team 222 (this is the phnum in the original post). My team could be in field1 or field2. What I need is the average of the score in field one if my team is in field1 and average of the score in field two if my team is in field two.

    In the given scenario above, for team 111 the average would be 90 and the average for team 222 would be 80.

    I am finding this very difficult to explain so please tell me if it didnt make sense.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you do me a favour please? could you not simplify?

    please give your actual table layouts, not "test" and "test1"

    use real column names, not "one" and "two"

    i'm still having trouble understanding what you're doing

    it's starting to look like home and away games, but i'd like to be sure

    and this business about looping in your procedure, running one phnum at a time, i'm just going to pretend i didn't hear that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha yep the idea in the example was home and away games. So here goes the more complicated explanation (I think)

    I have a table filled with call records. In this table, I have:

    Calling number
    Called number
    Packet loss for calling number
    Packet loss for called number

    Now the calling number and or called number may be one of my customers.

    I also have a table of all my customers' phone numbers.

    What I am attempting to do is have a table with all of my phone numbers and their average packet loss. Each phone number will have one entry.

    The procedure I am attempting to do should do the following:

    Loop through the customers' phone number table
    Look for the average packet loss when that number was on the calling side
    Look for the average packet loss when that number was on the called side
    Insert into a table and enter a record with "phone number, avg packet loss calling, avg packet loss called"

    Also if you have a suggestion on how to do this without looping through numbers please let me know. I am always open to suggestions! Thank you so much

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    INSERT INTO tablename (phonenumber, avgcallingloss, avgcalledloss)
      SELECT
          a.phonenumber
        , AVG(b.callingloss)
        , AVG(c.calledloss)
      FROM customers a
      LEFT OUTER JOIN cdr b
      ON a.phonenumber = b.callingnumber
      LEFT OUTER JOIN cdr c
      ON a.phonenumber = c.callednumber
      GROUP BY a.phonenumber

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    guido, if a.phonenumber = b.callingnumber for 15 rows, and a.phonenumber = c.callednumber for 25 rows, how many rows will your query return? that's right, 375

    what will that do to the averages?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, if a.phonenumber = b.callingnumber for 15 rows, and a.phonenumber = c.callednumber for 25 rows, how many rows will your query return? that's right, 375

    what will that do to the averages?
    Oops. I did that same thing yesterday myself. But of course, I tested my own work

    So how about this
    Code MySQL:
    INSERT INTO tablename (phonenumber, avgcallingloss, avgcalledloss)
      SELECT
          a.phonenumber
        , a.avgcallingloss
        , b.avgcalledloss
      FROM 
        (SELECT 
             a1.phonenumber
           , AVG(a2.callingloss) AS avgcallingloss
         FROM customers a1
         LEFT OUTER JOIN cdr a2
         ON a1.phonenumber = a2.callingnumber
         GROUP BY a1.phonenumber
        ) AS a
      INNER JOIN
        (SELECT 
             b1.phonenumber
           , AVG(b2.calledloss) AS avgcalledloss
         FROM customers b1
         LEFT OUTER JOIN cdr b2
         ON b1.phonenumber = b2.callednumber
         GROUP BY b1.phonenumber
        ) AS b
      ON a.phonenumber = b.phonenumber

  15. #15
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <snip /> thats one hell of a query. Let me test it out. Thank ya guys! I will let you know how it goes.

    EDIT:

    Its working perfectly! Thank you so much!!! I was stumped with this one. Once again members on SP saves the day
    Last edited by DaveMaxwell; Sep 10, 2009 at 10:35. Reason: removed offensive exclamation

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rash516 View Post
    Its working perfectly!
    guido's query will fail if there are no rows for callingnumber or no rows for callednumber, over the range of rows specified by the WHERE clause(s), if any

    if this never happens, you're okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That does happen if a customer does not make a call in the hour. In that case the stats table is set to default to 0 so it shows 0.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    which stats table?

    we had test and cdr, then test and test1, then customer...

    ... but no stats table

    you've over-simplified me right outta this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which stats table?

    we had test and cdr, then test and test1, then customer...

    ... but no stats table

    you've over-simplified me right outta this thread
    Haha sorry! The stats table would be the table that the averages are inserted into. The default value for the fields in that table are 0. The actual tables esp for the call details are about 150 fields so I tried to simplify it. Will post the whole thing next time! Thanks for all your help!

  20. #20
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    guido's query will fail if there are no rows for callingnumber or no rows for callednumber, over the range of rows specified by the WHERE clause(s), if any

    if this never happens, you're okay
    Ok so ignore my previous post. This has happened. How can I get around this? If it would help, I can put up the entire database schema up to see.


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
  •