我需要从两个表中提取列数据,对数据进行计算,并将结果另存为别名,然后将这些结果求和成其他别名以显示在php表中。我正在尝试通过在SELECT语句中创建派生表来实现此目的,但是它不起作用。我没有收到任何错误,但是我的表仅显示列标题。

码:

$sql = "SELECT x.company, x.stagestatus, x.shippeddate, SUM(x.totprice) as totalprice, SUM(x.sgtotquantity) as sgtotqty, SUM(x.sgtotalsqft) as sgtotsqft, SUM(x.avgsqftrev) as avgsqftrevenue, SUM(x.avgunitrev) as avgunitrevenue FROM (SELECT t1.company, t1.stagestatus, t1.shippeddate, t1.id, FORMAT(TRIM(LEADING '$' FROM t1.totalprice), 2) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotqauntity, FORMAT(SUM(t2.width * t2.height * t2.quantity ) /144, 2) AS sgtotalsqft, FORMAT((TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)), 2) as avgsqftrev, FORMAT((TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)), 2) AS avgunitrev
  FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
  WHERE (t2.invoiceid = t1.id)
  GROUP BY t1.id) x
WHERE x.stagestatus='Complete'
GROUP BY x.company ASC";


这段代码中断了,但是当我单独使用较小的代码时,它可以正常工作。

例如:

$sql="SELECT invoices.id, invoices.orderdate, invoices.stagestatus, FORMAT(TRIM(LEADING '$' FROM invoices.totalprice), 2) AS totalprice, clients.company, lineitems.invoiceid, SUM(lineitems.quantity) AS sgtotqty, FORMAT(SUM(lineitems.width * lineitems.height * lineitems.quantity ) /144, 2) AS sgtotsqft, FORMAT((TRIM(LEADING '$' FROM invoices.totalprice)/(SUM(lineitems.width * lineitems.height * lineitems.quantity ) /144)), 2) as avgsqftrevenue, FORMAT((TRIM(LEADING '$' FROM invoices.totalprice) / SUM(lineitems.quantity)), 2) AS avgunitrevenue
FROM clients
INNER JOIN invoices ON clients.id = invoices.clientid
INNER JOIN lineitems ON invoices.id = lineitems.invoiceid
WHERE (lineitems.invoiceid = invoices.id) AND invoices.orderdate BETWEEN '".$revenuefrom."' AND '".$revenueto."' AND invoices.stagestatus IN (". implode(',', array_map(function($item) {return '"' . $item . '"'; }, $revenue_check)) .")
GROUP BY invoices.id DESC";


此代码可以正常工作,并按invoices.id对所有数据进行分组。但是,项目需求已调整,现在所有内容都必须按invoices.company分组。当我只是尝试按invoices.company而不是invoices.id分组时,我的表已完成,但每个公司行的值都非常不准确(没有sum()正确)。

建表的PHP CODE:

$result = $conn->query($sql);


    echo "<table id='revenueReportA' align='center' class='report_DT'>
    <thead>
    <tr>

    <th>Customer</th>
    <th>Total Revenue</th>
    <th>Total SQ FT</th>
    <th>AVG Revenue Per SQ FT</th>
    <th>Total Number of Units</th>
    <th>AVG Revenue Per Unit</th>
    </tr>
    </head>";


 if ($result = $conn->query($sql)) {

   // fetch associative array
  while ($row = $result->fetch_assoc()) {

  echo "<tbody>";
  echo "<tr>";
  echo "<td>" . $row['company'] . "</td>";
  echo "<td>" ."$". $row['totalprice'] . "</td>";
  echo "<td>" . $row['sgtotsqft'] ."&nbsp;&nbsp;". "ft<sup>2</sup>". "</td>";
  echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
  echo "<td>" . $row['sgtotqty'] . "</td>";
  echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
  echo "</tr>";
  echo "</tbody>";
  }

   echo "</table>";

echo "<BR>";


感谢所有帮助。

谢谢,

最佳答案

我遇到了拼写错误和格式问题。通过格式化最终数据而不是在嵌入式SELECT语句中进行格式化,我的表数据是准确的。

成功的代码:

$sql = "SELECT x.company, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as sgtotqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.avgsqftrev), 2) as avgsqftrevenue, FORMAT(SUM(x.avgunitrev), 2) as avgunitrevenue FROM (SELECT t1.company, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus='Complete'
GROUP BY x.company ASC";


谢谢!!!

07-24 09:37
查看更多