有一个组\u concat，有一个组\u sum吗？

brc7rcf0 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(284)

qty是一个int。这个sql可以工作，但是我想求qty值的和，而不是连接它们。没有组和：叫别的什么吗？

SELECT sku as filterSKU, storenumber as storenumberSKU,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 0 THEN qty ELSE NULL END)) AS Monday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 1 THEN qty ELSE NULL END)) AS Tuesday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 2 THEN qty ELSE NULL END)) AS Wednesday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 3 THEN qty ELSE NULL END)) AS Thursday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 4 THEN qty ELSE NULL END)) AS Friday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 5 THEN qty ELSE NULL END)) AS Saturday,
      GROUP_CONCAT((CASE weekdayoftransaction WHEN 6 THEN qty ELSE NULL END)) AS Sunday
    FROM `reconciled`.sale 
    GROUP BY sku, storenumber;

这是table：

CREATE TABLE `sale` (
  `saleID` int(11) NOT NULL AUTO_INCREMENT,
  `EmployeeFirstName` varchar(45) DEFAULT NULL,
  `EmployeeLastName` varchar(45) DEFAULT NULL,
  `ProductDescription` varchar(45) DEFAULT NULL,
  `Unit` varchar(45) DEFAULT NULL,
  `SKU` varchar(45) DEFAULT NULL,
  `Qty` int(11) DEFAULT NULL,
  `UnitCost` decimal(10,3) DEFAULT NULL,
  `UnitPrice` decimal(10,3) DEFAULT NULL,
  `Manufacturer` varchar(45) DEFAULT NULL,
  `EmployeeNumber` varchar(45) DEFAULT NULL COMMENT 'The employee who handled the transaction',
  `LoyaltyNumber` varchar(45) DEFAULT NULL,
  `StoreNumber` varchar(45) DEFAULT NULL,
  `DateOfTransaction` date DEFAULT NULL,
  `TimeOfTransaction` time DEFAULT NULL,
  `DateOfTransactionString` varchar(45) DEFAULT NULL,
  `TimeOfTransactionString` varchar(45) DEFAULT NULL,
  `WeekdayOfTransaction` int(11) DEFAULT NULL,
  `MonthOfTransaction` int(11) DEFAULT NULL,
  `YearOfTransaction` int(11) DEFAULT NULL,
  `WeekdayNameOfTransaction` varchar(45) DEFAULT NULL,
  `MonthNameOfTransaction` varchar(45) DEFAULT NULL,
  `TotalPrice` decimal(10,3) DEFAULT NULL,
  PRIMARY KEY (`saleID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

sql mysql select

来源：https://stackoverflow.com/questions/51126277/theres-a-group-concat-is-there-a-group-sum

1条答案

按热度按时间

rqqzpn5f1#

这就是所谓的 sum . 您可以通过 group by 子句（您已经有了！）：

SELECT   sku AS filterSKU, storenumber AS storenumberSKU,
         SUM(CASE weekdayoftransaction WHEN 0 THEN qty ELSE NULL END) AS Monday,
         SUM(CASE weekdayoftransaction WHEN 1 THEN qty ELSE NULL END) AS Tuesday,
         SUM(CASE weekdayoftransaction WHEN 2 THEN qty ELSE NULL END) AS Wednesday,
         SUM(CASE weekdayoftransaction WHEN 3 THEN qty ELSE NULL END) AS Thursday,
         SUM(CASE weekdayoftransaction WHEN 4 THEN qty ELSE NULL END) AS Friday,
         SUM(CASE weekdayoftransaction WHEN 5 THEN qty ELSE NULL END) AS Saturday,
         SUM(CASE weekdayoftransaction WHEN 6 THEN qty ELSE NULL END) AS Sunday
FROM     `reconciled`.sale 
GROUP BY sku, storenumber;

赞(0）回复(0）举报 2021-06-20

我来回答

有一个组\u concat，有一个组\u sum吗？

1条答案

相关问题

热门标签

最新问答