I have table as below
DATE | JOB_ID |Name | Count --------------------|-----------|-----------|-------- 01-JAN-18 01:02:41 | JOB_1 | weight | 200 01-JAN-18 01:02:41 | JOB_1 | weight | 200 01-JAN-18 01:02:42 | JOB_1 | weight | 200 01-JAN-18 01:02:43 | JOB_1 | weight | 200 01-JAN-18 01:02:43 | JOB_1 | weight | 200 02-JAN-18 01:02:44 | JOB_2 | weight | 200 02-JAN-18 01:02:45 | JOB_2 | weight | 200 01-JAN-18 01:03:16 | JOB_1 | baseball | 192 01-JAN-18 01:11:15 | JOB_1 | hanescom | 37 01-JAN-18 01:11:15 | JOB_1 | hanescom | 200 01-JAN-18 01:11:16 | JOB_1 | hanescom | 200 01-JAN-18 01:11:17 | JOB_1 | hanescom | 200 01-JAN-18 01:11:17 | JOB_1 | hanescom | 200 01-JAN-18 01:11:18 | JOB_1 | hanescom | 200 03-JAN-18 01:11:25 | JOB_3 | hanescom | 200 03-JAN-18 01:11:26 | JOB_3 | hanescom | 200 03-JAN-18 01:11:26 | JOB_3 | hanescom | 200 01-JAN-18 01:11:27 | JOB_1 | hanescom | 189 01-JAN-18 01:11:28 | JOB_1 | wwbundle | 200 01-JAN-18 01:11:29 | JOB_1 | wwbundle | 200 01-JAN-18 01:11:29 | JOB_1 | wwbundle | 200 01-JAN-18 01:11:30 | JOB_1 | wwbundle | 200
I want to get below results,
DATE | JOB_ID |Name | sum(Count) --------------------|-----------|-----------|-------- 01-JAN-18 |JOB_1 |weight | 1000 02-JAN-18 |JOB_2 |weight | 400 01-JAN-18 |JOB_1 |baseball | 192 01-JAN-18 |JOB_1 |hanescom | 1226 03-JAN-18 |JOB_3 |hanescom | 600 01-JAN-18 |JOB_1 |wwbundle | 800
Advertisement
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;