I am querying two tables that produce the desired results. I need to insert elements conditionally from this results array into another table. Please excuse my lack of understanding, but I am fairly new to php and mysql.
the results fields are: bus_id, age, race, and sex. I want to insert the elements of these fields conditionally into another table that has the fields: bus_id, no_people, males, females, and ethnicity%.
The bus_id in results array does = the bus_id in the Insert table.
For example I want to count the people that are assigned to a particular bus_id, and determine the number of people, how many males, females, and ethnicity %.
My thought process might seem convoluted but please excuse me being a novice. If you could put me in the right direction it would be greatly appreciated.
Ok this is the table of results from the query of the first two tables that I have:
bus_name bus_id cus_name age race sex DHL 10 tom selleck 32 asian male Fedex 17 jennifer lopez 34 black female UPS 1 jet li 26 black male UPS 1 hugh douglas 40 black male UPS 1 ray lewis 40 white male UPS 1 michael vick 38 black male UPS 1 ed reed 44 white male UPS 1 joe flacco 17 white male UPS 1 bob dole 26 latin male UPS 1 deion sanders 15 white male UPS 1 bill clinton 15 latin male UPS 1 lebron james 41 white male
I want to insert these elements conditionally in another table called "comp_struct" that has the following fields: bus_id, males, females, ethnicity%( possibly will change it to diversity percentages)forget the ethnicity% for now.
So I would like for it look like this once completed.
bus_id no_people males females 1 10 10 0 17 1 0 1 10 1 1 0
This is how far I have made it with my code to produce the first table results.
$query1 = $db->query("SELECT bus.bus_name, bus.bus_id, bus_users.purch, bus_users.cus_name, bus_users.age, bus_users.race, bus_users.sex FROM bus, bus_users WHERE bus_users.purch = bus.bus_id ORDER BY bus.bus_name") or die($db->error);
while($result = mysqli_fetch_assoc($query1))
{
( "SELECT bus_id,
COUNT(*),
SUM(IF(sex='male', 1, 0)) AS males,
SUM(IF(sex='female', 1, 0)) AS females
FROM @query1
GROUP BY bus_id
INSERT INTO comp_struct (bus_id, no_people, males, females)
SELECT bus_id,
COUNT(*) AS no_people,
SUM(IF(sex='male', 1, 0)) AS males,
SUM(IF(sex='female', 1, 0)) AS females
FROM @query1
GROUP BY bus_id");
Needless to say this is not producing the desired results.
Ignoring the ethnicity field for the moment, you can get the values you want with:-
SELECT bus_id,
COUNT(*),
SUM(IF(sex='male', 1, 0)) AS males,
SUM(IF(sex='female', 1, 0)) AS females
FROM table_one
GROUP BY bus_id
and do an insert with:-
INSERT INTO table_two(bus_id, no_people, males, females)
SELECT bus_id,
COUNT(*) AS no_people,
SUM(IF(sex='male', 1, 0)) AS males,
SUM(IF(sex='female', 1, 0)) AS females
FROM table_one
GROUP BY bus_id
EDIT - based on the details you have given now a single statement will do the insert as follows (no need to select the rows separately first)
INSERT INTO table_two(bus_id, no_people, males, females)
SELECT bus.bus_id ,
COUNT(*) AS no_people,
SUM(IF(bus_users.sex ='male', 1, 0)) AS males,
SUM(IF(bus_users.sex ='female', 1, 0)) AS females
FROM bus
INNER JOIN bus_users
ON bus_users.purch = bus.bus_id
GROUP BY bus.bus_id
EDIT - this gets you the ethnicity as well:-
SELECT bus_id, no_people, males, females, GROUP_CONCAT(CONCAT(race, race_percentage, '%'))
FROM
(
SELECT Sub1.bus_id, Sub2.no_people, Sub2.males, Sub2.females, Sub1.race, (Sub1.race_count / Sub2.no_people) * 100 AS race_percentage
FROM
(
SELECT bus.bus_id ,
bus_users.race,
COUNT(*) AS race_count
FROM bus
INNER JOIN bus_users
ON bus_users.purch = bus.bus_id
GROUP BY bus.bus_id, race
) Sub1
INNER JOIN
(
SELECT bus.bus_id ,
COUNT(*) AS no_people,
SUM(IF(bus_users.sex ='male', 1, 0)) AS males,
SUM(IF(bus_users.sex ='female', 1, 0)) AS females
FROM bus
INNER JOIN bus_users
ON bus_users.purch = bus.bus_id
GROUP BY bus.bus_id
) Sub2
ON Sub1.bus_id = Sub2.bus_id
) Sub0
GROUP BY bus_id
SQL fiddle here for it:-