I have table as below
x
DATE | JOB_ID |Name | Count--------------------|-----------|-----------|--------01-JAN-18 01:02:41 | JOB_1 | weight | 20001-JAN-18 01:02:41 | JOB_1 | weight | 20001-JAN-18 01:02:42 | JOB_1 | weight | 20001-JAN-18 01:02:43 | JOB_1 | weight | 20001-JAN-18 01:02:43 | JOB_1 | weight | 20002-JAN-18 01:02:44 | JOB_2 | weight | 20002-JAN-18 01:02:45 | JOB_2 | weight | 20001-JAN-18 01:03:16 | JOB_1 | baseball | 19201-JAN-18 01:11:15 | JOB_1 | hanescom | 3701-JAN-18 01:11:15 | JOB_1 | hanescom | 20001-JAN-18 01:11:16 | JOB_1 | hanescom | 20001-JAN-18 01:11:17 | JOB_1 | hanescom | 20001-JAN-18 01:11:17 | JOB_1 | hanescom | 20001-JAN-18 01:11:18 | JOB_1 | hanescom | 20003-JAN-18 01:11:25 | JOB_3 | hanescom | 20003-JAN-18 01:11:26 | JOB_3 | hanescom | 20003-JAN-18 01:11:26 | JOB_3 | hanescom | 20001-JAN-18 01:11:27 | JOB_1 | hanescom | 18901-JAN-18 01:11:28 | JOB_1 | wwbundle | 20001-JAN-18 01:11:29 | JOB_1 | wwbundle | 20001-JAN-18 01:11:29 | JOB_1 | wwbundle | 20001-JAN-18 01:11:30 | JOB_1 | wwbundle | 200I want to get below results,
DATE | JOB_ID |Name | sum(Count)--------------------|-----------|-----------|--------01-JAN-18 |JOB_1 |weight | 100002-JAN-18 |JOB_2 |weight | 400 01-JAN-18 |JOB_1 |baseball | 19201-JAN-18 |JOB_1 |hanescom | 122603-JAN-18 |JOB_3 |hanescom | 60001-JAN-18 |JOB_1 |wwbundle | 800Advertisement
Answer
You can use date format method of sql to get the desired result
SELECT DATE_FORMAT(date, ‘%Y-%m-%d’) as date_column, JOB_ID, Name, sum(“count”) FROM tab GROUP BY date_column, JOB_ID, Name;