php - php/mysql 列的总和

Question

试图找出为什么我无法获取列的总和。在 mysql 列中是 Decimal(65,2) - 不确定这是否会导致任何冲突，并且 $ 符号已被删除，因为它们最初是从 csv 导入导出中存在的，但运行替换后一切都清楚了。

$sql = "SELECT DISTINCT ContactId, Date, First_Name, Last_Name, Referral_Partner,
Sale_Total, SUM(Sale_Total) AS sum_ttl 
    FROM PTLS1117v2 
     WHERE Referral_Partner = '$refpart' 
      AND Date BETWEEN '$pdate' AND '$pdate2'";

Answer 1

首先，一个测试表:

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s` decimal(10,2) NOT NULL DEFAULT 0.00,
  `t` varchar(10) NOT NULL DEFAULT 'xyzyy',
  PRIMARY KEY (`id`);

填充它:

MariaDB [test]> insert into t(s,t) values ('10.00','xyz'),('20.00','xyz'),('30.00','xyz'),('40.00','xyz'),('50.00','xyz');
Query OK, 5 rows affected (0.00 sec)

MariaDB [test]> insert into t(s,t) values ('60.00','abc'),('70.00','abc'),('80.00','abc'),('90.00','abc'),('100.00','abc');
Query OK, 5 row affected (0.00 sec)

MariaDB [test]> select s,t from t;
+--------+-----+
| s      | t   |
+--------+-----+
| 10.00  | xyz |
| 20.00  | xyz |
| 30.00  | xyz |
| 40.00  | xyz |
| 50.00  | xyz |
| 60.00  | abc |
| 70.00  | abc |
| 80.00  | abc |
| 90.00  | abc |
| 100.00 | abc |
+--------+-----+
10 rows in set (0.00 sec)

使用DISTINCT我得到这个:

MariaDB [test]> select distinct s,t,sum(s) from t where t='abc';
+-------+------+--------+
| s     | t    | sum(s) |
+-------+------+--------+
| 60.00 | abc  | 400.00 |
+-------+------+--------+
1 row in set (0.00 sec)

如果您尝试检索特定 Referral_Partner 的所有行(包含总计)，则必须使用两个查询 - 一个查询数据，一个查询总和。或者，在从表中检索行时计算总计