GROUP BY For reports

Hi all,

seems like I hit another brick wall and hope someone with great knowledge and expertise can safe me once again.

I have a mysql database that contains data captured for a car park. Its capturing data such as arrival time and departure time in unixtime, salesid and parking cost.
I need to create a reports page from that database, that can show me income by user defined year range, such as 2010, 2011 etc.

I would like to display this data in a human readable way, such as:

 
YEAR: 2010
MONTH: January
01 Feb,2010 || ¿3050
02 Feb,2010 || $4220
03 Feb,2010 || $1160
Etc for remaining entries

MONTH: February 
01 Mar,2010 || ¿690
02 Mar,2010 || $3140
03 Mar,2010 || $2890
Etc for remaining entries
Etc for remaining Months

YEAR: 2011
MONTH: January
01 Jan,2011 || ¿2770
02 Mar,2011 || $1200
03 Mar,2011 || $2390
04 Mar,2011 || $890
Etc for remaining entries
Etc for remaining Months

Open in new window


and so forth. I ham currently using this the following query to accomplish this, but fails badly. The code I am using is:

$yearSQL = "SELECT checkout_time  FROM shcp_salesitems  
WHERE
YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC";

$yearRLT = mysql_query($yearSQL) or die("Error: ".mysql_error());
$yearCOUNT = mysql_num_rows($yearRLT);
	
while($yearROW = mysql_fetch_array($yearRLT)){
								
	$year = date('Y',$yearROW['checkout_time']);
	
	echo('<br /><pre>YEAR: '.$year.'</pre>');
	
	
		$monthSQL =
		"SELECT checkout_time  FROM shcp_salesitems 
		WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($year) . "' 
		GROUP BY MONTH(FROM_UNIXTIME(checkout_time))
		ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC";
		
		$monthRLT = mysql_query($monthSQL) or die("Error: ".mysql_error());
		$monthCOUNT = mysql_num_rows($monthRLT);

			while($monthROW = mysql_fetch_array($monthRLT)){

				$month = date('m',$monthROW['checkout_time']);											
					
					echo('<pre>MONTH: '.date('F',$monthROW['checkout_time']).'</pre>');
						$daySQL =
						"SELECT checkout_time FROM shcp_salesitems 
						WHERE MONTH(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($month) . "' 
						GROUP BY MONTH(FROM_UNIXTIME(checkout_time))";
						$dayRLT = mysql_query($daySQL) or die("Error: ".mysql_error());
						$dayCOUNT = mysql_num_rows($dayRLT);
						
							while($dayROW = mysql_fetch_array($dayRLT)){
									
									$day = date('d',$dayROW['checkout_time']);
										$daySQL =
										"SELECT checkout_time,salesid, payed_amount FROM shcp_salesitems 
										WHERE DAY(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($day) . "'";
										$dayRLT = mysql_query($daySQL) or die("Error: ".mysql_error());
										$dayCOUNT = mysql_num_rows($dayRLT);
											while($dayROW = mysql_fetch_array($dayRLT)){
												
												echo('<pre>'.date('d M,Y',$dayROW['checkout_time']).' || &#3647;'.$dayROW['payed_amount'].'</pre>');
										
												}
										}
							}
					}

Open in new window


The output I am getting from my database is:

YEAR: 2014
MONTH: February
12 Feb,2014 || ¿150
MONTH: January
10 Jan,2014 || ¿1000

YEAR: 2013
MONTH: June
28 Jun,2013 || ¿677

YEAR: 2012
MONTH: August
21 Aug,2012 || ¿500

YEAR: 2011
MONTH: December
06 Dec,2011 || ¿130

YEAR: 2010
MONTH: October
05 Oct,2010 || ¿1500

Open in new window


The database contains 15 entries, so I am missing 9 entries somehow. I believe I am somehow creating to many GROUP BY and while loops somehow.
shcp-salesitems.sql
slickipAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, exactly that's what you need to do, with SUM() for the amount field:
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, FROM_UNIXTIME(checkout_time) checkout_time
, SUM(payed_amount) payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
, MONTH(FROM_UNIXTIME(checkout_time)) 
, MONTHNAME(FROM_UNIXTIME(checkout_time)) 
, FROM_UNIXTIME(checkout_time) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
, FROM_UNIXTIME(checkout_time) 
";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
	$month_name = $record["month_name"];
        $time = date('d M, Y',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month_name</pre>");
       		$prev_month = $month;
	}
	

	print("<pre>$time  || &#3647;$amount</pre>");
}

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't know why you are putting all those loops, you can do this in a single query + a bit of php, which looks like this (not tested for typose ...)
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, checkout_time
, payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
 checkout_time ";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
        $time = date('d',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month</pre>");
       		$month = $prev_month;
	}
	

	print("<pre>$time&#3647;$amount</pre>");
}
                                  

Open in new window

0
 
slickipAuthor Commented:
Hi Guy,

Thanks for the reply. Running the code doesn't give me to output format I am really after:
YEAR: 2014
MONTH: 2
12¿10
MONTH: 2
12¿10
MONTH: 2
12¿150
MONTH: 1
13¿2000
MONTH: 1
10¿1000

YEAR: 2013
MONTH: 6
29¿1200
MONTH: 6
28¿677

YEAR: 2012
MONTH: 8
23¿900
MONTH: 8
22¿10
MONTH: 8
21¿500

YEAR: 2011
MONTH: 12
07¿500
MONTH: 12
07¿1000
MONTH: 12
07¿10
MONTH: 12
06¿130

YEAR: 2010
MONTH: 10
15¿230
MONTH: 10
08¿1000
MONTH: 10
05¿1500

Open in new window


The formatting should look like this and I think your supplied code doesn't produce that.
Need this:
YEAR: 2010
MONTH: January
01 Jan, 2010 || ¿3050
02 Jan, 2010 || ¿4220
03 Jan, 2010 || ¿1160
Etc for remaining entries

MONTH: February 
01 Feb, 2010 || ¿690
02 Feb, 2010 || ¿3140
03 Feb, 2010 || ¿2890
Etc for remaining entries
Etc for remaining Months

YEAR: 2011
MONTH: January
01 Jan, 2011 || ¿2770
02 Jan, 2011 || ¿1200
03 Jan, 2011 || ¿2390
04 Jan, 2011 || ¿890
Etc for remaining entries
Etc for remaining Months

Open in new window


Thank you for explaining an alternative way instead of using loops.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see my error
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, FROM_UNIXTIME(checkout_time) checkout_time
, payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
 checkout_time ";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
	$month_name = $record["month_name"];
        $time = date('d M, Y',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month_name</pre>");
       		$prev_month = $month;
	}
	

	print("<pre>$time  || &#3647;$amount</pre>");
}

Open in new window

0
 
slickipAuthor Commented:
Hi Guy,

Thank you. This is awesome and exactly what I am after. I was wondering if your supplied code could be modify to handle this output:

Currently we have:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿10
12 Feb, 2014  || ¿10
12 Feb, 2014  || ¿150

Open in new window

Would it be possible to show only 1 entry of 12 Feb, 2014 but have a summary of this day? Like so:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿170

Open in new window


Perhaps with a  "GROUP BY DAY(FROM_UNIXTIME(checkout_time))", but I doubt this.

Thanks.
0
 
slickipAuthor Commented:
Hi Guy,

Thank you very much! I have slightly modified your code to this:
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, checkout_time
, SUM(payed_amount) payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
, DAY(FROM_UNIXTIME(checkout_time)) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
, FROM_UNIXTIME(checkout_time) 
";

Open in new window


to get the following output:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿660
MONTH: January
10 Jan, 2014  || ¿1000
13 Jan, 2014  || ¿2000

Open in new window


Before, your output resulted in the following:
YEAR: 2014
MONTH: February
01 Jan, 1970  || ¿150
01 Jan, 1970  || ¿10
01 Jan, 1970  || ¿10
01 Jan, 1970  || ¿490
MONTH: January
01 Jan, 1970  || ¿1000
01 Jan, 1970  || ¿2000

Open in new window


Nevertheless, its working flawlessly and I am awarding you the points for finding the right solution. Thank you again for your fast reply. I also learned a great deal form your code.

Regards.
0
 
slickipAuthor Commented:
Thank you again. Greatly appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.