我想有条件地得到当天销售报告的总和

Question

我想要得到单据的销售总额，在所有行的项目汇总一天。

http://sqlfiddle.com/#!9/61dc69/1

CREATE TABLE IF NOT EXISTS `sale` (
  `item_ID` int(6) unsigned NOT NULL,
  `'date` int(3) unsigned NOT NULL,
  `amount` varchar(200) NOT NULL,
  `qty` varchar(200) NOT NULL,
 )

INSERT INTO `sale` (`item_ID`, `date`, `amount`,  `qty `) VALUES

  ('2', '20190120', '10050','1'),
  ('2', '20190120', '10050','1'),
  ('4', '20190120', '9045','1'),
  ('6', '20190121', '10050','1'),
  ('4', '20190121', '20050','2')

select   item_ID,date,sum( amount) as amount,
sum(qty) as qty from sale
group by item_ID,date

我想作为过滤器计算每天的文档总数date，金额和数量是基于item and date作为过滤器的

我的预期输出如下：

item_ID  date      amount  qty  document total
2        20190120  20100    2    29145
4        20190120   9045    1    29145
6        20190121  10050    1    30100
4        20190121  20050    2    30100

Answer 1

你期望的输出

根据您的Suggestion...you告知，数据也是基于日期计算的。

CREATE TABLE sale (
  item_ID int NOT NULL,
  date VARCHAR(20) NOT NULL,
  amount varchar(200) NOT NULL,
  qty varchar(200) NOT NULL,
 )

INSERT INTO sale (item_ID, date, amount,  qty ) VALUES

  ('2', '09/09/2019', '10050','1'),
  ('2', '09/09/2019', '10050','1'),
  ('4', '09/09/2019', '9045','1'),
  ('6', '10/10/2019', '10050','1'),
  ('4', '10/10/2019', '20050','2')

您的表结构应如下所示：-

select   item_ID,
CAST(date AS DATE) date,
sum(Convert(int,amount)) as amount,
sum(Convert(int,qty)) as qty from sale
group by item_ID, CAST(date AS DATE)

​

​

注意：-我在SQL Server中按照指定的标记"SQL"执行此操作。

Answer 2

select qty_table.item_ID, qty_table.date,amount_table.sumamount, qty_table.sumqty, 
from 
(select date, item_ID, sum(qty) as sumqty from sale
group by date , item_ID ) qty_table 
join 
( select date,sum(amount) as sumamount from sale
group by date ) amount_table on  amount_table.date = qty_table.date

输出：

    | item_ID |     date | sumqty | sumamount |
    |---------|----------|--------|-----------|
    |       2 | 20190120 |      2 |     29145 |
    |       4 | 20190120 |      1 |     29145 |
    |       4 | 20190121 |      2 |     30100 |
    |       6 | 20190121 |      1 |     30100 |

http://sqlfiddle.com/#!9/61dc69/9