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 |