|
D
Dennis L author
I have a query that is displaying a field in the Total Sum row that I don't want. Any ideas how to change the query or change the display? 
SELECT
job_ID,
shop_built,
pipe_spec,
Total_Spools,
Total_Fabricated,
Balance_Remaining,
Perc_Fabricated,
Shipped_for_Lining,
Liner_Complete,
Remaining_for_Lining,
Perc_Lined,
Shipped_for_Painting_Tape,
Painting_Tape_Complete,
Remaining_to_Paint_Tape,
Perc_Painted_Tape,
Shipped_to_Site,
Remaining_to_Ship,
Perc_Delivered_to_Site
FROM
(
SELECT
job_ID,
shop_built,
pipe_spec,
COUNT(spool_no) AS Total_Spools,
COUNT(qc_compl) AS Total_Fabricated,
COUNT(spool_no) - COUNT(qc_compl) AS Balance_Remaining,
COUNT(qc_compl) / COUNT(spool_no) AS Perc_Fabricated,
COUNT(shipped_for_liner) AS Shipped_for_Lining,
COUNT(liner_complete) AS Liner_Complete,
COUNT(spool_no) - COUNT(liner_complete) AS Remaining_for_Lining,
COUNT(liner_complete) / COUNT(spool_no) AS Perc_Lined,
COUNT(shipped_to_paint) AS Shipped_for_Painting_Tape,
COUNT(paint_compl) AS Painting_Tape_Complete,
COUNT(spool_no) - COUNT(paint_compl) AS Remaining_to_Paint_Tape,
COUNT(paint_compl) / COUNT(spool_no) AS Perc_Painted_Tape,
COUNT(ship_date) AS Shipped_to_Site,
COUNT(spool_no) - COUNT(ship_date) AS Remaining_to_Ship,
COUNT(ship_date) / COUNT(spool_no) AS Perc_Delivered_to_Site,
job_id AS Sort_Order
FROM
shopspools
WHERE
hold_status NOT LIKE 'deleted'
GROUP BY
job_ID, shop_built, pipe_spec UNION ALL
SELECT
job_ID,
'pipe_spec' AS Total_Sum,
'Total Sum' AS shop_built,
COUNT(spool_no) AS Sum_Spools,
COUNT(qc_compl) AS Sum_Fabricated,
COUNT(spool_no) - COUNT(qc_compl) AS Total_Remaining,
COUNT(qc_compl) / COUNT(spool_no) AS Total_Perc_Fabricated,
COUNT(shipped_for_liner) AS Total_Shipped_for_Lining,
COUNT(liner_complete) AS Total_Liner_Complete,
COUNT(spool_no) - COUNT(liner_complete) AS Total_Remaining_for_Lining,
COUNT(liner_complete) / COUNT(spool_no) AS Total_Perc_Lined,
COUNT(shipped_to_paint) AS Total_Shipped_for_Painting_Tape,
COUNT(paint_compl) AS Total_Painting_Tape_Complete,
COUNT(spool_no) - COUNT(paint_compl) AS Total_Remaining_to_Paint_Tape,
COUNT(paint_compl) / COUNT(spool_no) AS Total_Perc_Painted,
COUNT(ship_date) AS Total_Shipped_to_Site,
COUNT(spool_no) - COUNT(ship_date) AS Total_Remaining_to_Ship,
COUNT(ship_date) / COUNT(spool_no) AS Total_Perc_Delivered_to_Site,
99999999 AS Sort_Order
FROM
shopspools
WHERE
hold_status NOT LIKE 'deleted'
GROUP BY
job_ID) AS groupedJobs
ORDER BY
Sort_Order |
|