Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

GROUP BY For reports

Posted on 2014-02-11
7
Medium Priority
?
222 Views
Last Modified: 2014-02-12
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
0
Comment
Question by:slickip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39852616
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
 

Author Comment

by:slickip
ID: 39853221
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39853245
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:slickip
ID: 39853367
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39853401
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
 

Author Comment

by:slickip
ID: 39853586
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
 

Author Closing Comment

by:slickip
ID: 39853591
Thank you again. Greatly appreciate it.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question