user3255412 user3255412 - 3 months ago 7
SQL Question

insert two SELECT statements into mysql table

hi can some one to tell me how to insert those two select statements into one table.

INSERT INTO test(Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway)

SELECT HomeTeam As Team, COUNT(HomeTeam) AS GamesPlayedHome, SUM(HomeGoals) AS GoalsScorredHome, SUM(AwayGoals) AS GoalsAcceptedHome, SUM(HomeRedCards) AS RedCardGotHome,
SUM(HomeGoals) / COUNT(HomeTeam) AS AvarageGoalsScorredHome, SUM(AwayGoals) / COUNT(HomeTeam) AS AvarageGoalsAcceptedHome
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC

SELECT COUNT(AwayTeam) AS GamesPlayedAway, SUM(AwayGoals) AS GoalsScorredAway, SUM(HomeGoals) AS GoalsAcceptedAway, SUM(AwayRedCards) AS RedCardGotAway,
SUM(HomeGoals) / COUNT(AwayTeam) AS AvarageGoalsScorredAway, SUM(AwayGoals) / COUNT(AwayTeam) AS AvarageGoalsAcceptedAway
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY AwayTeam ORDER BY AwayTeam ASC


I have tryed this

<?php
include("conf.php");
$query = "INSERT INTO test(Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway)
(SELECT HomeTeam As Team, COUNT(HomeTeam) AS GamesPlayedHome, SUM(HomeGoals) AS GoalsScorredHome, SUM(AwayGoals) AS GoalsAcceptedHome, SUM(HomeRedCards) AS RedCardGotHome,
SUM(HomeGoals) / COUNT(HomeTeam) AS AvarageGoalsScorredHome, SUM(AwayGoals) / COUNT(HomeTeam) AS AvarageGoalsAcceptedHome
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC )
UNION ALL
(SELECT COUNT(AwayTeam) AS GamesPlayedAway, SUM(AwayGoals) AS GoalsScorredAway, SUM(HomeGoals) AS GoalsAcceptedAway, SUM(AwayRedCards) AS RedCardGotAway,
SUM(HomeGoals) / COUNT(AwayTeam) AS AvarageGoalsScorredAway, SUM(AwayGoals) / COUNT(AwayTeam) AS AvarageGoalsAcceptedAway
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY AwayTeam ORDER BY AwayTeam ASC)
";
$data= mysqli_query($conn,$query) or die(mysqli_error($conn));
while($row = mysqli_fetch_assoc($data)){
foreach($row as $cname => $cvalue){
print "$cname: $cvalue\t";
}
print "\r\n";
}
?>


I get this error when i run it


Column count doesn't match value count at row 1


I also tried with multi query

<?php
include("conf.php");
//QUERY 1
$query = "INSERT INTO test(Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome)
SELECT HomeTeam As Team, COUNT(HomeTeam) AS GamesPlayedHome, SUM(HomeGoals) AS GoalsScorredHome, SUM(AwayGoals) AS GoalsAcceptedHome, SUM(HomeRedCards) AS RedCardGotHome,
SUM(HomeGoals) / COUNT(HomeTeam) AS AvarageGoalsScorredHome, SUM(AwayGoals) / COUNT(HomeTeam) AS AvarageGoalsAcceptedHome
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC;";
//QUERY 2
$query .= "INSERT INTO test(GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway)
SELECT COUNT(AwayTeam) AS GamesPlayedAway, SUM(AwayGoals) AS GoalsScorredAway, SUM(HomeGoals) AS GoalsAcceptedAway, SUM(AwayRedCards) AS RedCardGotAway,
SUM(HomeGoals) / COUNT(AwayTeam) AS AvarageGoalsScorredAway, SUM(AwayGoals) / COUNT(AwayTeam) AS AvarageGoalsAcceptedAway
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY AwayTeam ORDER BY AwayTeam ASC";

/* execute multi query */
if ($conn->multi_query($query)) {
do {
/* store first result set */
if ($result = $conn->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->free();
}
/* print divider */
if ($conn->more_results()) {
printf("-----------------\n");
}
} while ($conn->next_result());
}

/* close connection */
$conn->close();
?>


When i run this code it inserts the data but the second query inserts also empty rowsenter image description here

Please some help

Last code

<?php
include("conf.php");
$query = "SELECT querya.Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway
from
(SELECT HomeTeam As Team, COUNT(HomeTeam) AS GamesPlayedHome, SUM(HomeGoals) AS GoalsScorredHome, SUM(AwayGoals) AS GoalsAcceptedHome, SUM(HomeRedCards) AS RedCardGotHome,
SUM(HomeGoals) / COUNT(HomeTeam) AS AvarageGoalsScorredHome, SUM(AwayGoals) / COUNT(HomeTeam) AS AvarageGoalsAcceptedHome
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC)
querya inner join
(SELECT AwayTeam As Team, COUNT(AwayTeam) AS GamesPlayedAway, SUM(AwayGoals) AS GoalsScorredAway, SUM(HomeGoals) AS GoalsAcceptedAway, SUM(AwayRedCards) AS RedCardGotAway,
SUM(HomeGoals) / COUNT(AwayTeam) AS AvarageGoalsScorredAway, SUM(AwayGoals) / COUNT(AwayTeam) AS AvarageGoalsAcceptedAway
FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY AwayTeam ORDER BY AwayTeam ASC)
queryb on querya.Team = queryb.Team";
$data= mysqli_query($conn,$query) or die(mysqli_error($conn));
while($row = mysqli_fetch_assoc($data)){
foreach($row as $cname => $cvalue){
print "$cname: $cvalue\t";
}
print "\r\n";
}
?>

Answer

This is psuedo code at best, but it's a try.

Select (put all fields in here)
from (put first query in its entirey here) querya
inner join (put second query here, you will need to modify so it has a 'team' column) queryb
on querya.team = queryb.team

Get the idea? Union is the wrong term for what you are trying...Join is what you are looking for.

You had:

 "SELECT (Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway)
from ...

The brackets aren't needed.

 "SELECT Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway
from ... 

I editted your question, use it from there.

Comments