SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE COM_D2
(a1 nvarchar(10),a2 int, a3 int,a4 nvarchar(20));
INSERT INTO COM_D2 SELECT a1='F0052',a2= 1,a3=	200	,a4='1/3/15 6:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 1,a3=	200	,a4='1/3/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 5,a3=	1000,a4='1/3/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 7,a3=	1400,a4='1/3/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 8,a3=	1600,a4='1/3/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 6,a3=	1200,a4='1/3/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 5,a3=	1000,a4='1/4/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 6,a3=	1200,a4='1/12/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2= 5,a3=	1000,a4='1/12/15 8:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2=	5,a3=	1000,a4='1/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0052',a2=	5,a3=	1000,a4='1/24/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0002',a2=	4,a3=	800,a4='3/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0003',a2=	3,a3=	600,a4='2/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0002',a2=	7,a3=	1400,a4='3/15/15 10:50';					
INSERT INTO COM_D2 SELECT a1='F0005',a2=	8,a3=	1600,a4='1/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0002',a2=	7,a3=	1400,a4='2/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0003',a2=	5,a3=	1000,a4='1/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0005',a2=	6,a3=	1200,a4='1/18/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0002',a2=	10,a3=	2000,a4='3/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0003',a2=	12,a3=	2400,a4='2/15/15 9:50';					
INSERT INTO COM_D2 SELECT a1='F0005',a2=	13,a3=	2600,a4='1/15/15 9:50';					
21 rows affected

select a1,a2,a3,
CONVERT(varchar(12), convert(date,a4), 111) as a5
from COM_D2
where 1=1


a1
a2
a3
a5


F0052
1
200
2015/01/03

F0052
1
200
2015/01/03

F0052
5
1000
2015/01/03

F0052
7
1400
2015/01/03

F0052
8
1600
2015/01/03

F0052
6
1200
2015/01/03

F0052
5
1000
2015/01/04

F0052
6
1200
2015/01/12

F0052
5
1000
2015/01/12

F0052
5
1000
2015/01/15

F0052
5
1000
2015/01/24

F0002
4
800
2015/03/15

F0003
3
600
2015/02/15

F0002
7
1400
2015/03/15

F0005
8
1600
2015/01/15

F0002
7
1400
2015/02/15

F0003
5
1000
2015/01/15

F0005
6
1200
2015/01/18

F0002
10
2000
2015/03/15

F0003
12
2400
2015/02/15

F0005
13
2600
2015/01/15


select a1 as '商品代碼',sum(a2) as '個數' ,sum(a3) as '金額',
CONVERT(varchar(12), convert(date,a4), 111) as '銷售日期'
from COM_D2
where 1=1
group by a1,CONVERT(varchar(12), convert(date,a4), 111)
order by a1,CONVERT(varchar(12), convert(date,a4), 111)


商品代碼
個數
金額
銷售日期


F0002
7
1400
2015/02/15

F0002
21
4200
2015/03/15

F0003
5
1000
2015/01/15

F0003
15
3000
2015/02/15

F0005
21
4200
2015/01/15

F0005
6
1200
2015/01/18

F0052
28
5600
2015/01/03

F0052
5
1000
2015/01/04

F0052
11
2200
2015/01/12

F0052
5
1000
2015/01/15

F0052
5
1000
2015/01/24

a1	a2	a3	a5
F0052	1	200	2015/01/03
F0052	1	200	2015/01/03
F0052	5	1000	2015/01/03
F0052	7	1400	2015/01/03
F0052	8	1600	2015/01/03
F0052	6	1200	2015/01/03
F0052	5	1000	2015/01/04
F0052	6	1200	2015/01/12
F0052	5	1000	2015/01/12
F0052	5	1000	2015/01/15
F0052	5	1000	2015/01/24
F0002	4	800	2015/03/15
F0003	3	600	2015/02/15
F0002	7	1400	2015/03/15
F0005	8	1600	2015/01/15
F0002	7	1400	2015/02/15
F0003	5	1000	2015/01/15
F0005	6	1200	2015/01/18
F0002	10	2000	2015/03/15
F0003	12	2400	2015/02/15
F0005	13	2600	2015/01/15