Hi All, thank you for taking your time.
I am working on one of the request to create a report. we need to pull all projects for last 6 months with created_date, type of project along with few other fields and Total cost of each project. After my analysis, I have prepared the below SQL query to fetch the data. I have applied group by clause as we are fetching normal fields along with aggregate function (sum) for total cost but my user wants the "Total Cost" to display only on first row (meaning apply group by per project) but my group by is displaying per project and project type. They wanted to see the total cost per project irrespective of type.. other fields. please help.
SELECT DISTINCT PROJECT_NBR,
t1.ORIGINATOR_ID,
t1.CREATED_DATE,
START_DATE,
PROJECT_STATUS,
PROJECT CLOSE DATE,
V.TYPE,
V.APPROVED_BY,
V.APPROVED_DATE",
t1.PROJECT_CLOSED_DATE,
SUM (COST) AS "TOTAL COST"
FROM table1 t1, table2 t2, view1 V
WHERE V.PROJECT_NUMBER = t1.PROJECT_NBR
AND t1.PS_CODE = JPS.PS_CODE
AND t1.CREATED_DATE BETWEEN TO_DATE ('01/01/2016 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND TO_DATE ('06/30/2017 23:59:59','MM/DD/YYYY HH24:MI:SS')
AND (COST > 5 AND type IS NOT NULL)
GROUP BY PROJECT_NBR,
t1.ORIGINATOR_ID,
t1.CREATED_DATE,
START_DATE,
PROJECT_STATUS,
PROJECT CLOSE DATE,
V.TYPE,
V.APPROVED_BY,
V.APPROVED_DATE",
t1.PROJECT_CLOSED_DATE
ORDER BY 3;
Each project has multiple types, and cost is based on type of the project. so in result, we need to display the total cost for project irrespective of type.
data in tables:
project type Cost
1234 A 500
1234 B 10,000
1234 A 500
2345 A 100
o/p should be like:
project type Cost
1234 A 11,000
1234 B
2345 A 100
Many Thanks in advance for all your time...!!!