Jump to content

Two queries into one


_fr99rf_

Recommended Posts

Hi everyone, I'm having troubles with query from database. I want to select all from one table and select sum from second table in the same time. Is this possible?

Here is my script:

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT first_id, first_name FROM first";
$sql = "SELECT sum(total) as SumTotal FROM second";

$result = mysqli_query($conn, $sql);

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['first_id'].'</th>';
    echo '<th>'.$data['first_name'].'</th>';
    echo '<th>'.$data['SumTotal'].'</th>';
    echo '</tr>';
}

mysqli_close($conn);

Thanks!


Link to comment
Share on other sites

No I made that display sum total but from all city, I want to display from city that are in one common state.

 

For example:

 

You have table of cities that call table1, and you have table2 with group of another cities.

I wan to display total number of citizens form table1 and table2, but separated, not total sum from both.

 

I hope I did explain correctly...

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT * FROM table1, table2";
$result = mysqli_query($conn, $sql);

function numCitizens($conn) {
    $data = $conn->query("SELECT sum(citizens) as numCitizensFROM table2")->fetch_array();
    return $data["numCitizens"];
}
$sum = numCitizens($conn);

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['table1_id'].'</th>';
    echo '<th>'.$data['table1_name'].'</th>';
    echo '<th>'.$sum.'</th>';
    echo '</tr>';
}

mysqli_close($conn);
Edited by _fr99rf_
Link to comment
Share on other sites

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

Link to comment
Share on other sites

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

 

Well, thank you Max, I will give a try and let you know If I am on the right way... Thanks, and enjoy your second coffee :happy-04:  

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.