I have a query that displays a list or records from the database. E.g.
Mill Zone Reading
A B 22.1
A B NULL
A C 33.6
D H 11.1
D F 56.8
How do I combine the B zone's together and display as following in sql:
Mill Zone Reading
A B 22.1
C 33.6
D H 11.1
F 56.8
What I have tried:
WITH TEMP_TABLE AS
(SELECT
mz.mill,
sm.sugar_mill_order,
hcz.hczname as ZONE ,
s.name as STATION ,
round(cast(nvl(d.pvalue,d.dvalue)as float),2) AS Station_Reading,
round(avg(cast(nvl(d.pvalue,d.dvalue) as float)) over (partition by mz.mill),2) AS Station_Average,
round(avg(cast(nvl(d.pvalue,d.dvalue) as float)) over (partition by hcz.hczname),2) AS Zone_Average
FROM mill_by_zone mz
JOIN sugar_mills sm ON sm.sugar_mill_name = mz.mill
JOIN zone_by_station z ON z.zone = mz.zone
JOIN fca_hcz_info hcz ON hcz.hcz = z.zone
JOIN station_details s ON s.station_num = z.station
LEFT OUTER JOIN daily_data d ON s.station_num = d.station_num AND d.recorded = to_date('01/APR/2019', 'dd/mon/yyyy') AND d.dcode = 8
GROUP BY mz.mill, sm.sugar_mill_order,hcz.hczname, s.name, d.dvalue, d.pvalue, d.dcode
ORDER BY sm.sugar_mill_order, hcz.hczname,Station_Reading
),
TEMP_TABLE2 AS
(SELECT
round(avg(cast(nvl(d.pvalue,d.dvalue) as float)),2) AS Average_For_Report
FROM mill_by_zone mz
JOIN sugar_mills sm ON sm.sugar_mill_name = mz.mill
JOIN zone_by_station z ON z.zone = mz.zone
JOIN fca_hcz_info hcz ON hcz.hcz = z.zone
join station_details s ON s.station_num = z.station
LEFT OUTER JOIN daily_data d ON s.station_num = d.station_num AND d.recorded = to_date('01/APR/2019', 'dd/mon/yyyy') AND d.dcode = 8
)
SELECT * FROM TEMP_TABLE, TEMP_TABLE2;