# Need help with Bar Chart Query **URL:** \ **Category:** Ignition\ **Tags:** python, ignition79, database, vision, scripting\ **Created:** [March 16, 2022, 1:25am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243 "2022-03-16T01:25:57Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 16, 2022, 1:25am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/1 "2022-03-16T01:25:57Z")
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. ![barChart](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/d/6d2d7d0deb581fda5d4bd7cd339e9fa4489ded50.png) The query that the bar charts database gets it’s data from. ![barChartQuery](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/9/3996c6c8530288793a792983a372f832e94544fe.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [March 16, 2022, 1:28am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/2 "2022-03-16T01:28:02Z")
Oof, can you please use preformatted text `` to paste your full query? ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 16, 2022, 1:29am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/3 "2022-03-16T01:29:58Z")
```auto 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") ``` ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [March 16, 2022, 1:48am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/4 "2022-03-16T01:48:52Z")
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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [March 16, 2022, 11:31am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/5 "2022-03-16T11:31:57Z")
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. 🤪 EDIT: Either way, you’ll need to add something to the GROUP BY portion of the query to separate out the individual spirals. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [March 16, 2022, 11:40am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/6 "2022-03-16T11:40:46Z")
Excuse me but ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/7/07f860c8d1225ab607ee3df60e8de18db60554f0.jpeg) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [March 16, 2022, 12:01pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/7 "2022-03-16T12:01:31Z")
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: ```auto 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" ``` ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 16, 2022, 8:15pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/8 "2022-03-16T20:15:46Z")
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’. ![datepartError](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/8/d838d7c52858b988e91ecc19c32210b1c3b6d06a.png) ```auto 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}) + "' " ``` ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [March 16, 2022, 8:25pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/9 "2022-03-16T20:25:34Z")
I had meant to add `+ ", a.displaypath"` at the very end of the entire expression, after everything else. That part should be pretty agnostic. ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 17, 2022, 12:01am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/10 "2022-03-17T00:01:23Z")
I keep getting an argument error ![groupByError](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/f/2f34ae62f6935eba5cd65dd403480e40835419c8.png) ```auto 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)"), "") ``` ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [March 17, 2022, 12:23am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/11 "2022-03-17T00:23:34Z")
Try copying/pasting what I put in my post. 😉 If you look closely, its added after the entire original expression. ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 17, 2022, 1:57am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/12 "2022-03-17T01:57:02Z")
Haha, I didn’t see that. This is the error I get when doing the code after the switch script. ![exactCodeAfterScriptError](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/f/5f4de9dda566ce1cce8dfa3a4e8339a47fb74718.png) This is the switch script for the multistate ````auto 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"``` ```` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [March 17, 2022, 11:40am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/13 "2022-03-17T11:40:22Z")
> [@Cole\_Barthel](#): > > `"DATE_FORMAT(a.eventtime, '%W')(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)"` ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [March 17, 2022, 11:53am UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/14 "2022-03-17T11:53:20Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 17, 2022, 12:29pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/15 "2022-03-17T12:29:11Z")
Consider breaking up your SQL generation into fragments in separate custom properties. Then assemble in the final property using `stringFormat()` with the fragments. ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 17, 2022, 10:02pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/16 "2022-03-17T22:02:32Z")
@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. ![Irose_pturmeleditserror](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/c/bc20341cebf497af62664638eee41b62f42109b4.png) This is the error I get if I only add the multistate displaypath script to the new multistate. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e1803d32f2d05e0adc89a5b8be6f2228b63b04ee.png) The new multistate script for “Spiral 0” graph as suggested by @lrose. ```auto 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. ```auto 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 ````auto 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")``` ```` ---
**Author:** ![Cole\_Barthel](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Cole\_Barthel](https://forum.inductiveautomation.com/u/Cole_Barthel)\ **Post date:** [March 17, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/need-help-with-bar-chart-query/57243/17 "2022-03-17T22:03:23Z")
This is the Spiral 0 query for the graph with all of the spirals. ```auto 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") ```