Need help with group by

[racko]

Hey All,

 

I've searched the entire board and cannot seem to find the info I'm looking for.

 

Basically, what I'm trying to do is get the data to show only the totals for the months. I found some code around here that I've used but it only groups the date if they are exactly the same, for example, all 10/1/2008 gets grouped as one then all the 10/2/2008 gets grouped etc..

 

What I am looking for is all of October on one line all of November on the next line etc...

 

Here is what I've got so far:

 

$sql = mssql_query ("SELECT * FROM summary_sales_daily WHERE location_id = 100");
//GROUP BY summary_date");

   if(!mssql_num_rows($sql)){
           echo 'No Records Found';
   }

   else{



           while($row = @mssql_fetch_array($sql)){
                   $loc = $row["location_id"];

                   $total_temp = ($row["summary_value"] + $row["summary_value"]);

                   $total = round($total_temp, 2);

                   $x++;

                if($x == '1'){

                   $current_date = $row["summary_date"];
                   $current_total = $total;

                }else{

                   if ($row["summary_date"] == $current_date){
                        $current_total = $current_total + $total;

                   }else{

                        echo "<TR>";
                        echo "<td>$loc</td>";
                        echo "<td>$current_date</td>";
                        echo "<td>$current_total</td>";
                        echo "</TR>";
                        $current_date = $row["summary_date"];
                        $current_total = $total;
                  }
                }

           }
}

As you can see I've commented out group by because I keep getting this error and I can figure out how to fix it....

 

PHP Warning:  mssql_query():

message: Column 'summary_sales_daily_uid' is invalid in the select l

ist because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16

) in monthly_summary.php on line 46

PHP Warning:  mssql_query(): Query failed in monthly_summary.php on line 4

6

PHP Warning:  mssql_num_rows(): supplied argument is not a valid MS SQL-result resource in monthly_summary.php on line 53

 

 

Thanks in advance!!

[mtoynbee]

Hi, when doing grouped queries you need to specify the fields you want to query specifically

 

For example:

 

SELECT summary_date, SUM(summary_value) FROM summary_sales_daily WHERE location = 100 GROUP BY summary_date

 

This will return 2 cols, the date and the total value. Note the SUM function will only work with number fields otherwise use your method of adding them together using PHP (not recommended).

 

If the summary_date field is datetime then you can split the date by using MONTH(), DAY(), YEAR() functions that way you can return or query specific parts of the date i.e. for October

 

SELECT MONTH(summary_date), SUM(summary_value) FROM summary_sales_daily WHERE location = 100 AND MONTH(summary_date) = 10 GROUP BY MONTH(summary_date)

 

Hope this helps, let me know how you get on.

[crtreedude]

The above comment is correct. in a group by you have the aggregate fields (i.e. sum, count, etc) and you have the fields the determine the aggregate (all of the such and such a date, etc). think of the non aggregated fields as the criteria that creates the groupings.