Insert into table result of two quieries

[Rash516]

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

[guido2004]

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
);

[SJH]

Edit: ignore this...

[Rash516]

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!

[SirAdrian]

Get rid of the VALUES keyword and the extra set of parenthesis.

[guido2004]

Get rid of the VALUES keyword and the extra set of parenthesis.

You're right, I guess it was too late last night

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

[r937]

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

[guido2004]

True. I thought phnum was a value, but of course it isn't (no quotes around it).

[Rash516]

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.

CREATE TABLE `test` (                 
          `one` VARCHAR(25) DEFAULT NULL,     
          `two` VARCHAR(25) DEFAULT NULL      
        ) ENGINE=INNODB DEFAULT CHARSET=utf8

test1 = table containing my numbers and scores.

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.

[r937]

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

[Rash516]

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

[guido2004]

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

[r937]

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?

[guido2004]

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

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

[Rash516]

<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

[r937]

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

[Rash516]

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.

[r937]

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

[Rash516]

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!

[Rash516]

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.