MySql Query help

[shahzad429]

I have a table

 

CREATE TABLE `safarioldddata` (
`TableID` int(11) NOT NULL AUTO_INCREMENT,
`TourDate` date DEFAULT NULL,
`ClientName` varchar(255) DEFAULT NULL,
`NOAdults` int(10) DEFAULT '0',
`AdultAmount` float DEFAULT '0',
`AdultAvg` float DEFAULT '0',
`NOChildren` int(10) DEFAULT '0',
`ChildrenAmount` float DEFAULT '0',
`ChildAvg` float DEFAULT '0',
`UserID` int(10) DEFAULT NULL,
PRIMARY KEY (`TableID`)
)

 

 

And now i am working on daily report

i have used below query

select TourDate,sum(NOAdults) as NOAdult , sum(AdultAmount) as AdultTotal, avg(AdultAvg) as AdultAVG, sum(NOChildren) as NOChildren , sum(ChildrenAmount) as ChilrenTotal, avg(ChildAvg) as ChildrenAVG from safarioldddata where TourDate="2012/1/2"

 

it is working fine but the problem is as i am passing one days it give me on record like

 

 

TourDate NOAdult AdultTotal AdultAVG NOChildren ChilrenTotal ChildrenAVG

1/2/2012 183.00 34,253.38 206.24 24.00 1,966.60 36.36

 

 

there is a way where i will pass month and year and it will display report for whole that month.

 

 

Thanks,

Shahzad

Edited January 22, 2013 by shahzad429

[Barand]

select TourDate,sum(NOAdults) as NOAdult , sum(AdultAmount) as AdultTotal, avg(AdultAvg) as AdultAVG,
sum(NOChildren) as NOChildren , sum(ChildrenAmount) as ChilrenTotal, avg(ChildAvg) as ChildrenAVG
from safarioldddata
where YEAR(TourDate)=2012 AND MONTH(TourDate) = 1
GROUP BY TourDate

 

edit

PS Your Adult average should be total adult amount/ total adults. No need to store averages and you should not calculate averages of averages. Same goes for children

Edited January 22, 2013 by Barand

[shahzad429]

Thanks Barand it is working fine.

 

It is showing the days with the data can we make it in a way that it will display all days in that month if no data then it will show 0

 

and 1 more question sum and average are showing 8-9 digits after decimal. we have to put some thing in query so that it will only display 2 or 3 digit after decimal??

 

 

 

Thanks again for your help.

 

Thanks,

Shahzad

[Barand]

You can round the results to 2 places using ROUND() in your query

 

http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html#function_round

 

or you can format in PHP when outputting using number_format

[shahzad429]

Ok round() is fine.

 

and how can i show days with no data??

[Barand]

To get totals for every date in the month it needs input for every day. In the code below I have created a temporary table and loaded it with all the dates in the selected month. This table is then LEFT JOINed to your table to get the required zero totals where there is none for a date

 

<?php

function reportResults ($conn, $y, $m)
{
   $date = new DateTime("$y-$m-1");
   $daysInMonth = $date->format('t');
   $inc = new DateInterval('P1D');

   /***************************
   * create temp table with
   * all dates in the month
   ****************************/
   $sql = "CREATE TEMPORARY TABLE tmpdates (date DATE)";
   $conn->query($sql);

   $dateArr = array();
   for ($i=1; $i<=$daysInMonth; $i++) {
       $d = $date->format('Y-m-d');
       $dateArr[] = "('" . $d . "')";
       $date->add($inc);
   }
   $sql = "INSERT INTO tmpdates VALUES " . join (',', $dateArr);
   $conn->query($sql);


   $sql = "SELECT tmpdates.date,
       SUM(NOAdults) as NOAdult ,
       ROUND(SUM(AdultAmount),2) as AdultTotal,
       ROUND(SUM(AdultAmount)/SUM(NOAdults),2) as AdultAVG,
       SUM(NOChildren) as NOChildren ,
       ROUND(SUM(ChildrenAmount),2) as ChildrenTotal,
       ROUND(SUM(ChildrenAmount)/SUM(NOChildren),2) as ChildrenAVG
   FROM tmpdates
       LEFT JOIN safarioldddata ON tmpdates.date=safariolddata.TourDate
   GROUP BY date";

   return $conn->query($sql);

}

$mysqli = new mysqli('localhost','root','********','test');
$result = reportResults($mysqli, 2012, 1);                 // call the function and process the query results
while ($row = $result->fetch_row()) {
   // process result row
}
?>

Edited January 22, 2013 by Barand