How do I query multiple corresponding values from MySQL and add them together easily?

how to combine two select queries in sql with different columns
mysql combine two queries
how to combine two sql queries in one result
sql merge two tables with different fields
mysql merge two tables with same columns
mysql union all
how to retrieve data from two tables with one sql statement
sql multiple select statements one result

My database table holds width measurements for a specific font file. I'm trying to query the database for Letter_Box_Width for each corresponding letter within my $word variable and add them together to get a $total_word_box_width.

Unfortunately, I'm not sure how to easily query for multiple corresponding values and then add these values together afterward. I'm thinking I split $word, into an array... but then I'm unclear on how to query each value within the array. Will I have to do a separate query for each array value?

$word = "CHAD";

$array_to_query = str_split($word);

My table of data:

The result of $total_word_box_width using my example "CHAD" should result in a value of 4500.

Any help is much appreciated.

Something like this should do what you want. I'm assuming you're using mysqli and you have a database connection in $conn but that should be easy enough to change to what you actually have.

$word = 'chad';
$letters = array_unique(str_split(strtoupper($word)));
$query = "SELECT GROUP_CONCAT(Alpha) AS Alpha,
                 GROUP_CONCAT(Letter_Box_Width) AS Letter_Box_Width
          FROM letters 
          WHERE Alpha IN ('" . implode("','", $letters) . "')";
$result = mysqli_query($conn, $query) or die("unable to query database!");
if ($row = mysqli_fetch_assoc($result)) {
    $widths = array_combine(explode(',', $row['Alpha']), explode(',', $row['Letter_Box_Width']));
    $total_word_box_width = 0;
    foreach (str_split(strtoupper($word)) as $letter) {
        $total_word_box_width += $widths[$letter];
    }
}
echo $total_word_box_width;

I've included a call to strtoupper so that it doesn't matter if your word is in upper or lower case. If in fact you have separate entries in your table for upper and lower case characters, you would remove that call in the places it occurs i.e.

$letters = array_unique(str_split($word));
    foreach (str_split($word) as $letter) {

MySQL UNION: Combining Results of Two or More Queries, To combine result set of two or more queries using the UNION operator, these are the 2 and 3 are duplicates, the UNION removed them and kept only unique values. to use MySQL UNION statement to combine data from multiple queries into a All MySQL tutorials are practical and easy-to-follow, with SQL script and​  To sum up: How do I filter search results from bp_xprofile_data through a MySQL query matching multiple xprofile values? I’ve been working on this for 4 hours and I’ve done more research than I care to admit, especially because this is probably a much easier solution than I realize.

SELECT SUM(Letter_Box_Width) FROM <table_name> WHERE Alpha IN ("C", "H", "A", "D")

Does this not work?

Combine results from several SQL tables, Many times in a relational database the data you want to show in your query is in more than one table. How do you combine results from more than one SQL table​? it didn't provide an easy means for you to combine results from more than one query. I like to think of joins as the glue that puts the database back together. Now I want to request all values from both tables to encode them as JSON for the google line chart. In the table values I have 288 lines per day (every five minutes). In the table nitrate I have a single line per day with a timestamp, that does not match to any of the the other 288 timestamps.

What you need is a SQL aggregate function - thankfully the SQL specification contains just the one to suit your needs.

Without knowing your exact database system, I can't be sure of the specific code, but try something like the following :

SELECT SUM(Letter_Box_Width)
FROM <your table name>
WHERE Alpha in ('C', 'H', 'A', 'D')

How you parameterise the query so you can reuse it with other letter values also depends on your platform, but the use of SUM() should solve the heart of the problem.

Combine Information from Multiple MySQL Tables with JOIN , You can use a JOIN SELECT query to combine information from more than one The combined results table produced by a join contains all the columns from both by which table contributes all its rows, requiring the second table to match it. 2) MySQL INSERT – Inserting rows using default value example. If you want to insert a default value into a column, you have two ways: Ignore both the column name and value in the INSERT statement. Specify the column name in the INSERT INTO clause and use the DEFAULT keyword in the VALUES clause. The following example demonstrates the second way:

Use

SELECT Alpha, Letter_Box_Width FROM table WHERE Alpha IN ('C', 'H', 'A', 'D')

Then create an array with Alpha as key and calculate the Sum of Letter_Box_Width.

In this particular example, you can also calculate the SUM in MySQL:

SELECT SUM(Letter_Box_Width) FROM table WHERE Alpha IN ('C', 'H', 'A', 'D')

But this won't work if the same letter occurs more than once, like HELLO, this will count the letter 'L' only once.

In other words,

SELECT SUM(Letter_Box_Width) FROM table WHERE Alpha IN ('H', 'E', 'L', 'L', 'O')

is the same as

SELECT SUM(Letter_Box_Width) FROM table WHERE Alpha IN ('H', 'E', 'L', 'O')

Retrieving Records from Multiple Tables, It does no good to put records in a database unless you retrieve them eventually out how to write a query is determining the proper way to join tables together. As you gain experience, you'll be able to adapt joins more easily to new When a row from the left table matches a row from the right table, the  One such function is the “sum()” function. This function adds up two or more columns to give you a total added value for all these columns. The columns can be integers, floats, decimals or money data types. You can use the sum function to add up and display results or you can add up and store values in another database table column.

How to combine the results of multiple SQL queries using the , How to combine the results of multiple SQL queries using the UNION statement how it works, how we can use it to combine the result of queries run on How to use the UNION statement in a MariaDB/MySQL server; What are the the operation would be really easy: we would just calculate the sum of  The Consolidate is a useful tool for us to consolidate multiple worksheets or rows in Excel, so with this function, we can also summarize multiple rows based on the duplicates. Please do with the following steps: 1. Click a cell where you want to locate the result in your current worksheet. 2. Go to click Data > Consolidate, see screenshot: 3.

How to Join Two Tables in MySQL, How Joins in MySQL are used and how you can use the query to Join two table in MySQL. Cartesian Product; Self JOIN; FULL OUTER JOIN; Join Multiple Tables; Conclusion There are four easy ways to join two or more tables: Inner Join joins table in such a way that it only shows those results which  Union queries cannot be displayed in Design view. You build them by using SQL commands that you enter in a SQL view object tab. Create a union query by using two tables. On the Create tab, in the Queries group, click Query Design. A new query design grid opens, and the Show Table dialog box appears.

MySQL in a Nutshell: A Desktop Quick Reference, However, there are times when you may want to match multiple values. AS student, phone_home, phone_dorm FROM students JOIN course_rosters MySQL still accepts the results so that they may be used by the outer query. for the semester and he wants a list of their names and telephone numbers in advance. What I'm trying to do is the following concept: SELECT field1,field2,field3 FROM table1 where field3 like '%'+(SELECT distinct field from table2)+'%' But this is not allowed because like does not accept multiple values. How can I rewrite this query so I can wildcard match a field with multiple result values from another table?

Comments
  • for the word "chad" what values are you summing?
  • Letter_Box_Width
  • select sum(Letter_Box_Width) from XXX where Alpha in ('c','h','a','d'); ///you can make the in sting with implode()
  • Hi. I'm getting a "PHP Notice: Undefined index: c " for this line.. $total_word_box_width += $widths[$letter]; This is happening for each letter.
  • If you don't have lowercase letters in the table, you need to leave the calls to strtoupper in place.
  • Do you have lowercase letters in the table?
  • Yes, I have lowercase letters, and also spaces, apostrophes, and hyphens...
  • I'm confused. With the strtoupper removed it should work. Can you tell me what's in $query and $row?