Need help with Bar Chart Query

Below are photos of my bar graph and the query that the graphs gets it’s database from. Currently, the graph displays a bar for the COUNT sum of all_spiral_alarms (Spiral 1, Spiral 2, and Spiral 3). I’ve tried everything I know to do to get a bar displayed for each individual spiral alarms instead of a sum of all alarms, but it’s not quite good enough.

How do I get the COUNT of each spiral (Spiral 1, Spiral 2, Spiral 3) displayed in their own bars?

The bar chart.

The query that the bar charts database gets it’s data from.

Oof, can you please use preformatted text </> to paste your full query?

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) all_spiral_alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +   
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + "Spiral 1" + "' " +
"	OR a.displaypath = '" + "Spiral 2" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	OR a.displaypath = '" + "Spiral 3" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",
	
"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) all_spiral_alarms " +  
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + "Spiral 1" +  "' " +
"	OR a.displaypath = '" + "Spiral 2" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	OR a.displaypath = '" + "Spiral 3" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " + 
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " AS label, " + 
"	COUNT(*) all_spiral_alarms " +
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + "Spiral 1" +  "' " + 
"	OR a.displaypath = '" + "Spiral 2" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	OR a.displaypath = '" + "Spiral 3" +  "' " +
"	AND a.eventtype = 0 " +
"	AND a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT 1")

So first things first, that is one impressive expression, any reason you didn’t use a named query? I think what you need to do is have a separate query(s) that you do your sum by spiral and then Union with your main queries. Im not ashamed to admit that i didn’t fully follow your expression, so no code will be forthcoming from me.

edit: Ooh, this is what you found at the exchange in your other topic.

2 Likes

Cole, did you mention what db you’re using? I use the same analysis charts, but pared them down to just the postgres ones. My eyes started to cross after a looking at the orginals. :crazy_face:

EDIT: Either way, you’ll need to add something to the GROUP BY portion of the query to separate out the individual spirals.

1 Like

Excuse me but
image

2 Likes

The {Alarms By Selection.Selection.groupBy} property (The Day of week / Day / Hour of Day multistate button) you can add what else to group by, tagging it on the end of the expression. If you put a display path on your alarms, to put a friendly name to them (and IMO, you should) it would look something like:

switch({Bypass Analysis.dbType},

"MYSQL", "MSSQL", "POSTGRES", 

switch({Bypass Analysis.Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATE_FORMAT(a.eventtime, '%W')",

"DAY(a.eventtime)", 

"HOUR(a.eventtime)", 

"HOUR(a.eventtime)"),
	
switch({Bypass Analysis.Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATENAME(DW, a.eventtime)",

"DATEPART(DAY, a.eventtime)", 

"DATEPART(HOUR, a.eventtime)", 

"DATEPART(HOUR, a.eventtime)"),

switch({Bypass Analysis.Alarms By Selection.Selection.controlValue},

0, 1, 2,

"to_char(a.eventtime, 'MM-DD Dy')",

"to_char(a.eventtime, 'MM-DD')",

"to_char(a.eventtime, 'MM-DD HH24')", 

"to_char(a.eventtime, 'MM-DD HH24')"),

"") 
+ ", a.displaypath"
1 Like

So I believe my DB type is MySQL.

After adding the displayPath to the end of each expression in the multistate button (which I basically copied from my selQuery), I get a syntax error for the selQuery script that I embedded in a reply above for ‘DATEPART’.

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATE_FORMAT(a.eventtime, '%W')",

"DAY(a.eventtime)", 

"HOUR(a.eventtime)", 

"HOUR(a.eventtime)"),

"'a.displaypath' = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' "+
	
switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATENAME(DW, a.eventtime)",

"DATEPART(DAY, a.eventtime)", 

"DATEPART(HOUR, a.eventtime)", 

"DATEPART(HOUR, a.eventtime)"),

"'a.displaypath' = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' "+

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"EXTRACT(DOW FROM a.eventtime)",

"EXTRACT(DAY FROM a.eventtime)", 

"EXTRACT(HOUR FROM a.eventtime)", 

"EXTRACT(HOUR FROM a.eventtime)"),

"")

+"'a.displaypath' = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' "

I had meant to add + ", a.displaypath" at the very end of the entire expression, after everything else. That part should be pretty agnostic.

I keep getting an argument error

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATE_FORMAT(a.eventtime, '%W')(a.displaypath)",

"DAY(a.eventtime)", 

"HOUR(a.eventtime)",

"HOUR(a.eventtime)",

"+ (a.displaypath)"),
	
switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATENAME(DW, a.eventtime)",

"DATEPART(DAY, a.eventtime)", 

"DATEPART(HOUR, a.eventtime)",

"DATEPART(HOUR, a.eventtime)",

"+ (a.displaypath)"),

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"EXTRACT(DOW FROM a.eventtime)",

"EXTRACT(DAY FROM a.eventtime)", 

"EXTRACT(HOUR FROM a.eventtime)", 

"EXTRACT(HOUR FROM a.eventtime)",

"+ (a.displaypath)"),

"")

Try copying/pasting what I put in my post. :wink:

If you look closely, its added after the entire original expression.

Haha, I didn’t see that.
This is the error I get when doing the code after the switch script.

This is the switch script for the multistate

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATE_FORMAT(a.eventtime, '%W')(a.displaypath)",

"DAY(a.eventtime)(a.displaypath)", 

"HOUR(a.eventtime)(a.displaypath)",

"HOUR(a.eventtime)(a.displaypath)"),

	
switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATENAME(DW, a.eventtime)(a.displaypath)",

"DATEPART(DAY, a.eventtime)(a.displaypath)", 

"DATEPART(HOUR, a.eventtime)(a.displaypath)",

"DATEPART(HOUR, a.eventtime)(a.displaypath)"),

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"EXTRACT(DOW FROM a.eventtime)(a.displaypath)",

"EXTRACT(DAY FROM a.eventtime)(a.displaypath)", 

"EXTRACT(HOUR FROM a.eventtime)(a.displaypath)", 

"EXTRACT(HOUR FROM a.eventtime)(a.displaypath)"),

"")

+ ", a.displaypath"```

This does not resolve to valid SQL, and I’m not sure it is what @JordanCClark was trying to do either. It would need to be something like:

"DATE_FORMAT(a.eventtime, '%W') + (a.displaypath)"

1 Like

It looks like it’s used in more than one spot in the query expression. I’ll take a closer look after my morning meetings. And coffee. Maybe breakfast.

1 Like

Consider breaking up your SQL generation into fragments in separate custom properties. Then assemble in the final property using stringFormat() with the fragments.

@lrose @pturmel

First off, I am so sorry!

After both of these edits, this is the error I get. I’ll embed the codes down below.

I’ll give context to as much as I can. There is an “all spirals” button that sets a property to “Spiral 0”. Also 3 more that set it to “Spiral 1”, “Spiral 2”, “Spiral 3”. When the property is “Spiral 0”, a new graph with it’s own query, multistate, and database becomes visible. When the property is not “Spiral 0”, the original bar graph with the original query, multistate, and database is visible.

I get this error for all 3 spirals when I add the multistate displaypath script to the new and original multistate.

This is the error I get if I only add the multistate displaypath script to the new multistate.

The new multistate script for “Spiral 0” graph as suggested by @lrose.

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATE_FORMAT(a.eventtime, '%W')+(a.displaypath)",

"DAY(a.eventtime)+(a.displaypath)", 

"HOUR(a.eventtime)+(a.displaypath)",

"HOUR(a.eventtime)+(a.displaypath)"),

	
switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"DATENAME(DW, a.eventtime)+(a.displaypath)",

"DATEPART(DAY, a.eventtime)+(a.displaypath)", 

"DATEPART(HOUR, a.eventtime)+(a.displaypath)",

"DATEPART(HOUR, a.eventtime)+(a.displaypath)"),

switch({Alarm Analysis (1).Alarms By Selection.Selection.controlValue},

0, 1, 2,

"EXTRACT(DOW FROM a.eventtime)+(a.displaypath)",

"EXTRACT(DAY FROM a.eventtime)+(a.displaypath)", 

"EXTRACT(HOUR FROM a.eventtime)+(a.displaypath)", 

"EXTRACT(HOUR FROM a.eventtime)+(a.displaypath)"),

"")

The final property using stringFormat as suggested by @pturmel.

stringFormat("%s, %s, %s", {Root Container.Alarm Analysis (1).spiral_id_data_extra_1}, {Root Container.Alarm Analysis (1).spiral_id_data_extra_2}, {Root Container.Alarm Analysis (1).spiral_id_data_extra_3})

This is the original query for the graph for one individual spiral at a time. I believe the error is referring to this query

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

"SELECT " + 
{Alarm Analysis (1).Alarms By Selection 1.Selection.groupBy} + " label, " + 
"	COUNT(*) alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +   
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",
	
"SELECT " + 
{Alarm Analysis (1).Alarms By Selection 1.Selection.groupBy} + " label, " + 
"	COUNT(*) alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " + 
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT " + 
{Alarm Analysis (1).Alarms By Selection 1.Selection.groupBy} + " AS label, " + 
"	COUNT(*) alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT 1")```

This is the Spiral 0 query for the graph with all of the spirals.

switch({Alarm Analysis (1).dbType},

"MYSQL", "MSSQL", "POSTGRES", 

"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) all_spiral_alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +   
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data_extra}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

	
"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) all_spiral_alarms " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data_extra}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " + 
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",


"SELECT " + 
{Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " AS label, " + 
"	COUNT(*) all_spiral_alarms " +
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis (1).startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis (1).endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +    
"	AND a.eventtype = 0 " +
"	AND a.displaypath = '" + escapeSQL({Alarm Analysis (1).spiral_id_data_extra}) + "' " +
"GROUP BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis (1).Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT 1")