ASPRunner manual
Using SQL to shape chart data

Charts are all about visualizing your data. Charts need data fields that store numeric values however using aggregate functions you can build charts on any data. Consider the following Orders table: Customer Country Total Andrew Peters USA $250 Katie Bradshow Australia $85 Jeff Simpson USA $150 Arnold Matteus Germany $120 Arnold Matteus Germany $160 Jeff Montgomery GB $150 Andrew Peters USA $65 Jeff Simpson USA $95 Luke Sohu France $40 Jeff Montgomery GB $120 Example 1: Total Sales per country Query select Country, sum(total) as STotal from Orders group by country order by 2 desc Results: Country Stotal USA $560 Germany $280 GB $270 Australia $85 France $40 Chart: Example 2: Number of orders per country Query: select Country, count(total) as CTotal from Orders group by country order by 2 desc Results: Country CTotal USA 4 Germany 2 GB 2 Australia 1 France 1 Chart:   Example 3: Shaping your data more complex way This example shows how to use GROUP BY in conjunction with INNER JOIN. For example we have the following data and like to display a diagram that illustrates how many flags each client has. clientid flag 1001 Green 1001 Green 1001 Green 1001 Green 1001 Amber 1001 Amber 1001 Red 1002 Green 1002 Amber 1002 Amber 1002 Amber 1002 Red 1003 Green 1003 Amber 1003 Red To shape our data we use the following SQL query select a.clientid, a.green,b.red, c.amber from (select count(flag) as green, clientid from sensorstatus where flag='Green' group by clientid) a inner join (select count(flag) as red, clientid from sensorstatus where flag='Red' group by clientid) b on a.clientid=b.clientid inner join (select count(flag) as amber, clientid from sensorstatus where flag='Amber' group by clientid) c on a.clientid=c.clientid; which gives us the following results: clientid green red amber 1001 5 1 2 1002 2 1 3 1003 1 1 1 Related info: Interactive SQL tutorial