How to order a bar chart by largest to smallest value?

Ignition
1

I have created a bar chart which shows the downtime for different machines. I have a subquery which puts the data into separate columns for each machine and then the main query sums each of those columns. How would I then sort these columns from greatest to least? I figured it would be an order by clause but I cant seem to find the correct way to set it up. Here is the query code:

select top 10
Equipment,
SUM(SUBQUERY.DARB) as DARB,
SUM(SUBQUERY.Dryer) as Dryer,
SUM(SUBQUERY.DTI) as DTI,
SUM(SUBQUERY.Dumper) as Dumper,
SUM(SUBQUERY.FEXW1) as FEXW1,
SUM(SUBQUERY.FEXW2) as FEXW2,
SUM(SUBQUERY.FEXW3) as FEXW3,
SUM(SUBQUERY.FEXW4) as FEXW4,
SUM(SUBQUERY.FEXW5) as FEXW5,
SUM(SUBQUERY.FEXW6) as FEXW6,
SUM(SUBQUERY.FEXW7) as FEXW7,
SUM(SUBQUERY.FEXW8) as FEXW8,
SUM(SUBQUERY.FEXW9) as FEXW9,
SUM(SUBQUERY.FEXW10) as FEXW10,
SUM(SUBQUERY.FEXW11) as FEXW11,
SUM(SUBQUERY.FEXW12) as FEXW12,
SUM(SUBQUERY.FEXW13) as FEXW13,
SUM(SUBQUERY.FEXW14) as FEXW14,
SUM(SUBQUERY.FEXW15) as FEXW15,
SUM(SUBQUERY.FEXW16) as FEXW16,
SUM(SUBQUERY.FEXW17) as FEXW17,
SUM(SUBQUERY.FEXW18) as FEXW18,
SUM(SUBQUERY.FEXW19) as FEXW19,
SUM(SUBQUERY.FEXW20) as FEXW20,
SUM(SUBQUERY.FEXW21) as FEXW21,
SUM(SUBQUERY.FEXW22) as FEXW22,
SUM(SUBQUERY.FEXW23) as FEXW23,
SUM(SUBQUERY.FEXW24) as FEXW24,
SUM(SUBQUERY.FEXW25) as FEXW25,
SUM(SUBQUERY.Filler) as Filler,
SUM(SUBQUERY.Labeler) as Labeler,
SUM(SUBQUERY.MissingCapDetector) as 'MissingCapDetector',
SUM(SUBQUERY.Robot) as Robot,
SUM(SUBQUERY.TableT) as TableT,
SUM(SUBQUERY.Washer) as Washer,
SUM(SUBQUERY.CapStripper) as 'CapStripper'
from
(
	select
	Equipment,
	SUM (case when Equipment = 'DARB' then + (Downtime/60) end) as DARB,
	SUM (case when Equipment = 'Dryer' then (Downtime/60) end) as Dryer,
	SUM (case when Equipment = 'DTI' then (Downtime/60) end) as DTI,
	SUM (case when Equipment = 'Dumper' then (Downtime/60) end) as Dumper,
	SUM (case when Equipment = 'FEXW 1' then (Downtime/60) end) as FEXW1,
	SUM (case when Equipment = 'FEXW 2' then (Downtime/60) end) as FEXW2,
	SUM (case when Equipment = 'FEXW 3' then (Downtime/60) end) as FEXW3,
	SUM (case when Equipment = 'FEXW 4' then (Downtime/60) end) as FEXW4,
	SUM (case when Equipment = 'FEXW 5' then (Downtime/60) end) as FEXW5,
	SUM (case when Equipment = 'FEXW 6' then (Downtime/60) end) as FEXW6,
	SUM (case when Equipment = 'FEXW 7' then (Downtime/60) end) as FEXW7,
	SUM (case when Equipment = 'FEXW 8' then (Downtime/60) end) as FEXW8,
	SUM (case when Equipment = 'FEXW 9' then (Downtime/60) end) as FEXW9,
	SUM (case when Equipment = 'FEXW 10' then (Downtime/60) end) as FEXW10,
	SUM (case when Equipment = 'FEXW 11' then (Downtime/60) end) as FEXW11,
	SUM (case when Equipment = 'FEXW 12' then (Downtime/60) end) as FEXW12,
	SUM (case when Equipment = 'FEXW 13' then (Downtime/60) end) as FEXW13,
	SUM (case when Equipment = 'FEXW 14' then (Downtime/60) end) as FEXW14,
	SUM (case when Equipment = 'FEXW 15' then (Downtime/60) end) as FEXW15,
	SUM (case when Equipment = 'FEXW 16' then (Downtime/60) end) as FEXW16,
	SUM (case when Equipment = 'FEXW 17' then (Downtime/60) end) as FEXW17,
	SUM (case when Equipment = 'FEXW 18' then (Downtime/60) end) as FEXW18,
	SUM (case when Equipment = 'FEXW 19' then (Downtime/60) end) as FEXW19,
	SUM (case when Equipment = 'FEXW 20' then (Downtime/60) end) as FEXW20,
	SUM (case when Equipment = 'FEXW 21' then (Downtime/60) end) as FEXW21,
	SUM (case when Equipment = 'FEXW 22' then (Downtime/60) end) as FEXW22,
	SUM (case when Equipment = 'FEXW 23' then (Downtime/60) end) as FEXW23,
	SUM (case when Equipment = 'FEXW 24' then (Downtime/60) end) as FEXW24,
	SUM (case when Equipment = 'FEXW 25' then (Downtime/60) end) as FEXW25,
	SUM (case when Equipment = 'Filler' then (Downtime/60) end) as Filler,
	SUM (case when Equipment = 'Labeler' then (Downtime/60) end) as Labeler,
	SUM (case when Equipment = 'Missing Cap Detector' then (Downtime/60) end) as MissingCapDetector,
	SUM (case when Equipment = 'Robot' then (Downtime/60) end) as Robot,
	SUM (case when Equipment = 'Table' then (Downtime/60) end) as TableT,
	SUM (case when Equipment = 'Washer' then (Downtime/60) end) as Washer,
	SUM (case when Equipment = 'Cap Stripper' then (Downtime/60) end) as CapStripper
	from WestExit_DT_Test
	where t_stamp BETWEEN (:StartDate) AND (:EndDate)
	Group By Equipment, (Downtime/60)
) AS SUBQUERY
Group By Equipment

Here is a photo of the Bar Chart currently:

image
image1136×532 26.2 KB

I only want it to show the top 10 from highest to lowest downtime.

2

The ORDER BY will only work if the data is in a tall format. Your query returns a single row. What about something like:

SELECT *
FROM
	(SELECT Equipment, SUM(Downtime/60) as Downtime
	 FROM WestExit_DT_Test
	 WHERE t_stamp BETWEEN (:StartDate) AND (:EndDate)
	 GROUP BY Equipment
	) AS SUBQUERY
ORDER BY Downtime DESC
LIMIT 10
2 Likes
3

Thank you very much I was clearly overthinking it! This worked perfectly except for the LIMIT 10 part but I just used Select Top 10* instead and that worked! Thank you!

1 Like
4

Different Strokes for differtent DBs. :wink:

Plus, I didn’t notice the TOP 10 in your post. Heh.

1 Like