bubbadawg

I am using the following query in an attempt to get total number(sum) of slides retrieving the max number from each project, however I am receiving the following error (#1111 - Invalid use of group function). Projects | Slides Air Stairs | 15 Air Stairs | 09 Baseball Game | 22 Cargo | 47 Cargo | 31 Here's the query: SELECT COALESCE(project,'Total') as Project, SUM(MAX(slides)) as Slides FROM projects_tbl WHERE date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY Project with ROLLUP If I remove the SUM(), then the it works, however, I do not get an accurate total for all of the projects/slides. Thanks in advance for any and all replies.

Hi, Thanks for your assistance. I think that will work.

I have a recordset that returns real estate data. I need to sort and group the data on two fields property_id and sold (boolean). So I need to have all the properties that are available (not sold) listed by property_id (desc) first and then the remaining (sold) properties listed by property_id (desc). Thanks for the assistance.

Thanks, you definitely did! I think this should work. The only issue I think I will run into is the string being replaced (planes) may or may not have a comma after it depending on where it falls in the main string (i.e. first word or last)

I have a field in a database which contains a string. The string can contain multiple words separated by commas (i.e. planes, 757, Boeing). What's the best way to retrieve all records that have a particular string (word) in a field, remove that string(word) from the field and then update the field and record. For example, I want to find all records that have 'planes' in the field 'asset_lightboxes'. Remove any instances of 'planes' from the string and update the record. In the example above the updated field would be - '757, Boeing'. Thanks for the assistance.

Hello All, I just installed XAMPP on my home machine for windows 7. I am trying to access the mysql database using Flex and AMFPHP. I am getting the following error when I attempting to connect via Flex / PHP - Access denied for user 'ODBC'@'localhost' (using password: NO); See PHP code below: Access denied for user 'ODBC'@'localhost' (using password: NO) <?php class LightBoxes{ public function __constructor(){ $connect = mysql_connect("localhost","root","mypassword"); mysql_select_db("digitalassets",$connect); } function read(){ $query = "SELECT * FROM lightboxes_tbl"; $result = mysql_query($query) or die(mysql_error()); return mysql_error(); } } } ?> ' I have researched this via Google and can not seem to find a solution that will work on my machine - I am also experiencing the same issue on my work machine as well. I set up the user 'root' with a password via the XAMPP security page. I currently don't have the user 'ODBC'. Any information to help resolve this issue is certainly appreciated.

For anyone who is interested, using COALESCE will supply the label, in this case 'Total' for the last row. Here's the complete query: SELECT COALESCE(project,'Total')project, SUM(hours) as Hours FROM `projects_tbl` GROUP BY project WITH ROLLUP;

Thanks, that did it! The resulting total is returned with a NULL value for the row name. Is it possible for me to set a name for the row?

I have the following query: SELECT DISTINCT project, SUM(hours) as Hours FROM `projects_tbl` GROUP BY project This returns data similar to the following: project Hours dev#1 5 dev#2 15 dev#3 12 dev#4 8 dev#5 10 How can I get the sum of all the Hours (50) from all the projects in the returned query? Thanks for any and all replies.

Okay, I think this will return what I need unless there is a better / more efficient way to do it. SELECT project, MAX(date), MIN(date), SUM(hours) from projects_tbl GROUP BY project

Can my entire query be done in one SQL statement? This is where I seem to be getting tripped up as I am not sure of the exact syntax to get my desired results. Thanks.

I have a projects_tbl setup with the following fields: project_id, project, hours, and date. I am trying to run a query that will return each (unique) project, total hours for the entire project and number of days the project took to complete (difference between first date entry and last date entry). I am able to come up with part of the query: "SELECT project, SUM(hours) AS hours from projects_tbl GROUP BY project." However, I am thinking that the remaining part might be above my pay grade as I am not a MySQL expert. Any help is greatly appreciated.

One additional question. How about the inverse, retrieving the date from the database and converting it to a different format? Would I need to do that in the SQL statement or change the format via php? Thanks.

Perfect thanks. I am using a date control in Flex so there should be no issue with validating the date.

What's the best way to convert a date formatted as mm/dd/yyyy to a format that can be stored in mySQL (yyyy-mm-dd). The reason I want to convert is that when pulling the data back out of the database I need to sort it on date. If there is a better way to do this I am open to that as well. Thanks for any and all replies.