How to Count Sales of Different Variant and Sizes-CodePudding

I'm trying to count total number of sales of a particular product and its variant and sizes.

Current Code :

$arr2 = array();
$counter = 0;
for($arr = 0; $arr < count($product_id); $arr  ){
    if (in_array($product_id[$arr], $arr2)) {
          $counter;
        continue;
    }
    else{
        $arr2[] = $product_id[$arr];
    }
}
echo 'number of duplicates: '.$counter;
print_r($arr2);

Result of above code

number of duplicates: 85
Array ( 
 [0] => Array ( [id] => 1089 [variant] => Crunchy Classic [size] => 1 Kg )
 [1] => Array ( [id] => 1089 [variant] => Creamy Dark Chocolate [size] => 1 Kg ) 
 [2] => Array ( [id] => 1090 [variant] => Universal [size] => Universal ) 
 [3] => Array ( [id] => 1089 [variant] => Creamy Dark Chocolate [size] => 340 gm )
 [4] => Array ( [id] => 1089 [variant] => Crunchy Classic [size] => 340 gm ) 
 )

I want the result to be like

Array ( 
 [0] => Array ( [id] => 1089 [variant] => Crunchy Classic [size] => 1 Kg  [count] => 2)
 [1] => Array ( [id] => 1089 [variant] => Creamy Dark Chocolate [size] => 1 Kg  [count] => 1) 
 [2] => Array ( [id] => 1090 [variant] => Crunchy Classic [size] => 340gm [count] => 2) 
 [3] => Array ( [id] => 1089 [variant] => Creamy Dark Chocolate [size] => 340 gm [count] => 1)
 [4] => Array ( [id] => 1089 [variant] => Crunchy Classic [size] => 340 gm [count] => 2) 
 )

To simplify more, My current data is this

ProductID | SKU  | Variant | size.  |
-------------------------------------
 1089     | 123  |   A     | 1KG    |
 1089     | 123  |   B     | 250gm  |
 1089     | 123  |   B     | 1KG    |
 1089     | 123  |   A     | 1KG    |
 1089     | 123  |   C     | 250gm  |
 1090     | 124  |   C     | 250gm  |
 1090     | 124  |   C     | 1KG    |
 1090     | 124  |   C     | 1KG    |
 1090     | 124  |   C     | 1KG    |
-------------------------------------

So the result should be

ID    | SKU | Variant | Size   | Count |
1089  | 123 |   A     | 1KG    |   2   | 
1089  | 123 |   B     | 1KG    |   1   | 
1089  | 123 |   B     | 250gm  |   1   | 
1089  | 123 |   C     | 250gm  |   2   | 
1089  | 123 |   C     | 1KG    |   3   |

Records are in mutiple of variant and sizes and i need to count different variant and sizes count

Model

ProductID, SKU, Price, Variant, Size, TransactionID, Status

here is the basic query :

DB::table('orders')->whereBetween('created_at', [$date['start'], $date['end']])->get();

CodePudding user response：

$arr = [
    [ 'productID' => 1089, 'SKU' => 123, 'variant' => 'A', 'size' => '1KG' ],
    [ 'productID' => 1089, 'SKU' => 123, 'variant' => 'B', 'size' => '250gm' ],
    [ 'productID' => 1089, 'SKU' => 123, 'variant' => 'B', 'size' => '1KG' ],
    [ 'productID' => 1089, 'SKU' => 123, 'variant' => 'A', 'size' => '250gm' ],
    [ 'productID' => 1089, 'SKU' => 123, 'variant' => 'C', 'size' => '1KG' ],
    [ 'productID' => 1090, 'SKU' => 124, 'variant' => 'C', 'size' => '250gm' ],
    [ 'productID' => 1090, 'SKU' => 124, 'variant' => 'C', 'size' => '1KG' ],
    [ 'productID' => 1090, 'SKU' => 124, 'variant' => 'C', 'size' => '1KG' ],
    [ 'productID' => 1090, 'SKU' => 124, 'variant' => 'C', 'size' => '1KG' ],
];

$result = [];

foreach ($arr as $item) {
  $index = -1;
  for ($i = 0; $i < count($result); $i  ) {
    if ($item['variant'] === $result[$i]['variant'] && $item['size'] === $result[$i]['size']) {
      $index = $i;
      break;
    }
  }
  if ($index > -1) {
    $result[$index]['count']  = 1;
  } else {
    $result[] = [ ...$item, 'count' => 1 ];
  }
}

CodePudding user response：

You can use :

The COUNT() function returns the number of records returned by a select query.
groupBy()

$orders = DB::table('orders')
    ->selectRaw('ProductID as ID, SKU, Variant, Size, COUNT(*) as "Count"')
    ->whereBetween('created_at', [$date['start'], $date['end']])
    ->groupBy('ID', 'Variant')
    ->get();

If you have message :

incompatible with sql_mode=only_full_group_by

You need to change 'strict' => false from database.php.