如何在表格中获得最近12个月的销售小计 - How can I get the last 12 months sales subtotals in a table?

How can I get the last 12 months sales subtotals in a table?

本文关键字：12个最近表格 | 更新日期: 2023-09-27

到目前为止，我有这段代码，它为我提供了给定月份每个产品的小计。

 <?php
    include("dbaseconn.php"); 


    $query = "SELECT product, SUM(qty) FROM sh_orders GROUP BY product"; 
    $result = mysql_query($query) or die(mysql_error());
    // Print out result
    while($row = mysql_fetch_array($result)){
      echo "<table width='1050'>"  ;
      echo "<td width='400'>";
        echo "". $row['product']. " ";
      echo "</td>";
      echo "<td>";
      echo "". $row['SUM(qty)']. "" ;
      echo "</td>";
        echo "<br />";
    }
    ?>

表结构

CREATE TABLE IF NOT EXISTS `sh_orders` (
  `building` varchar(50) NOT NULL,
  `qty` varchar(5) NOT NULL,
  `product` varchar(50) NOT NULL,
  `order_id` bigint(5) NOT NULL AUTO_INCREMENT,
  `unik` varchar(50) NOT NULL,
  `order_date` varchar(25) NOT NULL,
  `co_id` varchar(20) NOT NULL,
  UNIQUE KEY `unik` (`unik`),
  UNIQUE KEY `id` (`order_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

我想显示过去12个月的销售额-SUM(数量(为我做了这件事，但我需要分别查看每个月的每个产品及其当月的总销售额。我需要在一个表格中显示每个月的每个产品和当月的总额

EXPECTED RESULT
Month:    January 2012    Feb 2012   Marc 2012
Product1: 124                105         76
Product2:  34                 23         56
Product3:  12                  5          6

我是新手，所以如果我问一百万个问题，请耐心等待。

SELECT product,sum(qty),DATE_FORMAT(product_month, '%Y-%m') as month 
FROM sh_orders 
WHERE product_month BETWEEN DATE_SUB(now(), INTERVAL 12 MONTH) 
AND now() group by  DATE_FORMAT(product_month, '%Y%m'),product

每个产品在同一个月和不同的日子都有一些数量。第一组按月计算，相同的产品数量每月相加。按产品划分的第二组，避免同月产品重复

将查询更改为

  $query = "SELECT product, SUM(qty) FROM sh_orders where Product_month between '2011-01-01' and '2012-01-01' GROUP BY product";

这是您需要的查询：

SELECT order_date, product, SUM(qty) as sum_qty
FROM sh_orders 
WHERE order_date > DATE_SUB( CURDATE(), INTERVAL 1 YEAR)
GROUP BY YEAR(order_date), MONTH(order_date), product

sqlfiddle

结果是以下格式的数组：

array(
    array(
        'order_date' => '2012-09-02',
        'product'    => 'p2',
        'sum_qty'    => '2'
    ),
    array(
        // etc...
    )
);

您只需要循环它并将结果显示为一个表。如果您在创建表时遇到问题，最好在此处接受答案并提出新问题，因为它与html、loops和arrays更相关。