MySQL syntax help

shawjames

Hello,
I am trying to find rows in my database that have the same value for var1 and the sum of all rows value or var2 is equal to 100.

So I know there are 10 rows in my table, all with the same var1 and the sum off all 10 var2 values is 100. I need to know what the var1 value is.

Anyone know how I could do a search for this?

Thanks!!
-Shaw

rachel2004

Use the WHERE clause in your SELECT statement:

SELECT column1, column2
FROM table
WHERE column1 = 'some_value'
AND
column2 = 'another_value';

j_miguel_y

You will need to queries. Unlike oracle, you cannot use subqueries. It should be like this kinda?!?!

$query = "SELECT * from table_name WHERE some_attribute = '$var1'";

$result = mysql_query =($query);

$num_rows = mysql_num_rows($result);

while ($qrow = mysql_fetch_array($result)){
$sum += $qrow['some_attribute]; // adds all the contents of that column

}

if ($num_rows > 0 && $sum == 100)
$flag = 'true';

// The last if statement checks if var1 exists in any of the rows, and if the sum is equla to 100

Mike

rachel2004

Originally posted by j_miguel_y
You will need to queries. Unlike oracle, you cannot use subqueries. It should be like this kinda?!?!

MySQL v4.1 supports subqueries. One of the major additions to the product. It's still in beta though.

shawjames

Yea, I could figure out how to do it with a php script... but I thought there might be an easy way to do it directly. Is there a MySQL query that could do this?

rachel2004

What is it that mean when you say do it "directly"? To me, that means to SSH to the MySQL server and issue the query from the mysql monitor. If that's the case, only you would know if you have that capability as that is something you've set up, your sys admin has set up, or your hosting company has set up.

And when you ask if there is a MySQL query that could do this, I'm not clear what you mean. Both j_miguel_y and I have written sample queries. What do you consider a "MySQL query" in this case?

Roger_Ramjet

try the following query:

SELECT var1, SUM(var2), COUNT(var1) FROM table
GROUP BY var1
HAVING ((SUM(var2) = 100) AND (COUNT(var1) = 10))

This will return a list of var1 s that have 10 rows in the table 'count(var1)=10' AND whose var2 s add up to 100 'sum(var2)=100'

This is standard SQL syntax for what you want. Syntax may vary a little for MySQL

Look in a MySQL manual or go here for more information on the GROUP BY operator used in a SELECT statement, (group by and having are half way down the page)

http://dev.mysql.com/doc/mysql/en/SELECT.html#IDX1542

shawjames

Perfect, thanks Roger that was exactly what I was looking for.

Need to study up on the HAVING clause

Roger_Ramjet

Glad I could help. Here's a tip I always use myself.

Get yourself a copy of a desktop database that has a good Query-by-Example such as Access. Copy your MySQL database tables and structure to it. Access has a drag and drop query designer that you can use to create and test your queries. Once you have the correct result set coming out, you can go to the SQL View and copy and paste the SQL statement into your script. A desktop database will also return more info when you have an error in your query.

Even though it will need some editing, I find it a lot quicker to edit than type the whole query from scratch, especially when there are a lot of fields included by name. I do this all the time.

You should generate a set of test data so that you know what the result of your query should be before you test it. In your example, you should know that the var1 list should have 3 entries, and know which those entries are. You could then test by editing 1 of the var2s and see if the var1 disappears from the list, etc.

Every database engine has it's own flavour or dialect of SQL, so you will have to watch out for different syntax: eg Access uses SELECT INTO instead of MAKE TABLE; it inserts extra () and can rename fields and expressions in inconvenient ways. The trade-off is that it has a good Help system for info on SQL statements and operators, and even sometimes lists when Jet SQL does not conform to the SQL RFC.