Grouping count by month

[Andy2024]

I'm trying to get sales counts by month but having issues. Here is the dump

array(2) { [0]=> object(stdClass)#80 (2) { ["Counter"]=> int(61) ["Month"]=> NULL } [1]=> object(stdClass)#81 (2) { ["Counter"]=> int(5) ["Month"]=> int(5) } }

how would i get

{ ["Counter"]=> int(61) ["Month"]=> int(5) }

Here is the code i am using

MySQL
 

public function get_sales_list() {
	return $this->db->select("SELECT * FROM ".PREFIX."tservices WHERE InGraph = '1'");
}
public function get_sales_count($id) {
	return $this->db->select("SELECT COUNT(".PREFIX."tprojects.IdProject) as Sales
							  FROM 
										   ".PREFIX."tprojects 
							  WHERE 
										   ".PREFIX."tprojects.IsActive = '1'
							  AND 
										   ".PREFIX."tprojects.Service = '$id'
							  GROUP BY 
							  MONTH(".PREFIX."tprojects.Start), YEAR(".PREFIX."tprojects.Start)");
}

PHP
 

$servGraph =  $this->_query->get_sales_list();

if(isset($servGraph)) {
	$sales = array();
	foreach($servGraph as $row) 
	{
		$count = $this->_query->annual_sales($row->IdService);
		foreach($count as $key => $val)
		{
			array_push($sales, $val);
		}
	}
}
$data['sales-list'] = $sales;

var_dump($data['sales-list']);

 

Thanks in advance for any help or suggections

[Barand]

To get the month in your results you need to select it. Your query only selects the count.

[Andy2024]

54 minutes ago, Barand said:

To get the month in your results you need to select it. Your query only selects the count.

That is the issue because this also give the same result
 

	public function get_sales_count($id) {
		return $this->db->select("SELECT COUNT(".PREFIX."tprojects.IdProject) as Sales,
									     MONTH(".PREFIX."tprojects.Start) AS Month
								  FROM 
										 ".PREFIX."tprojects 
								  WHERE 
										 ".PREFIX."tprojects.IsActive = '1'
								  AND 
										 ".PREFIX."tprojects.Service = '$id'
								  GROUP BY 
    									 MONTH(".PREFIX."tprojects.Start), YEAR(".PREFIX."tprojects.Start)");
	}

array(2) { [0]=> array(2) { ["Count"]=> int(61) ["Month"]=> NULL } [1]=> array(2) { ["Count"]=> int(5) ["Month"]=> int(5) } }

I cannot understand where thus is coming from either because all entries are may 2024

Quote

 array(2) { ["Count"]=> int(5) ["Month"]=> int(5) }

 

[Barand]

9 minutes ago, Andy2024 said:

array(2) { [0]=> array(2) { ["Count"]=> int(61) ["Month"]=> NULL } [1]=> array(2) { ["Count"]=> int(5) ["Month"]=> int(5) } }

That seems to suggest you have 61 entries with an invalid date (null) and 5 entries for month 5 (may).

I don't understand where "Count" index is coming from in that results array as the count() has an column alias "Sales".

[Andy2024]

Quote

SELECT COUNT(".PREFIX."tprojects.IdProject) as Sales,

 

Their are a total of 69 rows in table ".PREFIX."tprojects as per phpmyadmin

[Andy2024]

i think it may be returning them on different rows in the array

 

Quote

array(2) { [0]=> array(2) { ["Count"]=> int(61) ["Month"]=> NULL } [1]=> array(2) { ["Count"]=> int(5) ["Month"]=> int(5) } }

[0]=> array(2) { ["Count"]=> int(61) ["Month"]=> NULL }

[1]=> array(2) { ["Count"]=> int(5) ["Month"]=> int(5) } }

[0]is returning the count and [1] is returning the month (5) May!

[Barand]

Execute your query in phpmyadmin. What do you get?

[Andy2024]

Found an issue, i haven't converted the datetime string to sql datetime somewhere

 

[Barand]

Are you also doing something silly like storing your "start" column as type VARCHAR instead of type DATE?

[Andy2024]

Sorted thanks

I ended up  putting

									if(!empty($counts->Counter) && !empty($counts->Month)) {
										array_push($sales, array('Count' => $counts->Counter, 'Month' => $counts->Month));
									}

At least its giving the correct count

[Barand]

I was going to suggest

mysql> select * from tprojects;
+-----------+---------+----------+------------+
| IdProject | service | IsActive | start      |
+-----------+---------+----------+------------+
|         6 |     123 |        1 | 2024-05-01 |
|         7 |     123 |        1 | 2024-05-02 |
|         8 |     123 |        1 | 2024-05-03 |
|         9 |     123 |        1 | 2024-05-04 |
|        10 |     123 |        1 | 2024-05-05 |
|        11 |     123 |        1 | NULL       |
|        12 |     123 |        1 | NULL       |
|        13 |     123 |        1 | NULL       |
|        14 |     123 |        1 | NULL       |
|        15 |     123 |        1 | NULL       |
|        16 |     123 |        1 | NULL       |
|        17 |     123 |        1 | NULL       |
+-----------+---------+----------+------------+


mysql> SELECT COUNT(IdProject) as Sales
    ->      , DATE_FORMAT(start, '%M %Y') AS Month
    -> FROM tprojects
    -> WHERE IsActive = '1'
    ->       AND Service = 123
    ->       AND MONTH(start) IS NOT NULL
    -> GROUP BY month;
+-------+----------+
| Sales | Month    |
+-------+----------+
|     5 | May 2024 |
+-------+----------+

 

[Andy2024]

I have had to go about it a totally different way because I need figures for each job type so have got this

 

"SELECT ".PREFIX."tservices.IdService,
	".PREFIX."tservices.Name,
	".PREFIX."tservices.GraphColor,
	".PREFIX."tprojects.Service,
COUNT(".PREFIX."tprojects.IdProject) as Sales,
	".PREFIX."tprojects.Finish
FROM 		 
	".PREFIX."tservices
LEFT JOIN ".PREFIX."tprojects ON ".PREFIX."tservices.IdService = ".PREFIX."tprojects.Service

WHERE
	".PREFIX."tservices.InGraph = '1'
GROUP BY MONTH(".PREFIX."tprojects.Finish), YEAR(".PREFIX."tprojects.Finish)
ORDER BY ".PREFIX."tprojects.Finish DESC LIMIT 0, 12"

Then the PHP is

 

if(!empty($data['legend'])) {
  foreach($data['legend'] as $r1) {
  	if(isset($r1->Name)) {
      $sales = $this->_query->get_sales_count($r1->IdService);
      if(!empty($sales)) {
          foreach($sales as $r2) {
          $count[] = $r2->Sales; 
      }
      $legend[] = array('label' => $r1->Name,
      'backgroundColor' => $r1->GraphColor,
      'data' => implode(',',$count));
      }

    }
  }
}

But the json its outputting

[{"label":"Installation","backgroundColor":"#2bca2d","data":"8,19,11,3,49"},{"label":"Repair","backgroundColor":"#cfca3a","data":"8,19,11,3,49,1,3,7"}]

instead of

[{"label":"Installation","backgroundColor":"#2bca2d","data":"8,19,11,3,49"},{"label":"Repair","backgroundColor":"#cfca3a","data":"1,3,7"}]

Does anyone have any idea how to solve this?

Thanks in advance

[Barand]

Why don't you group by job type, month

[Andy2024]

I tried that but still gives the same results

 

[Andy2024]

Bit of a breakthrough

I've altered the PHP to

				
				if(!empty($data['legend'])) {
					foreach($data['legend'] as $r1) {
						if(isset($r1->Name)) {
							$sales = $this->_query->get_sales_count($r1->IdService);
							if(!empty($sales)) {
								foreach($sales as $r2) {
									$count[$r1->Name][] = $r2->Sales; 
								}
								$legend[] = array('label' => $r1->Name,
																  'backgroundColor' => $r1->GraphColor,
																  'data' => implode(',',$count[$r1->Name]));
							}
							
						}
					}
				}

 

and that has given me

[{"label":"Installation","backgroundColor":"#2bca2d","data":"8,19,11,3,49"},{"label":"Repair","backgroundColor":"#cfca3a","data":"1,3,7"}]

which looks ok until its placed in the char then it adds everything to the wrong month and only 1 item per month

[Barand]

I have no idea how your chart software works but if you provide five data values (8, 19, 11, 3, 49) for one type and three data values (1, 3, 7) for the other, how is it supposed to know which months those values are for?

You have a very weird x-axis for that chart (the month sequence is 6-5-4-3-2-1-12-11-10-9-8-7). The norm is to put them in chronological order.