Using SQL to shape chart data

 

Charts are all about visualizing the numbers or percentages - they require data fields that store numeric values. However, you can use SQL queries to build charts using almost any initial data.

 

Consider the following Orders table:

 

Example 1: Total sales per country

SQL query:

select Country, sum(total) as STotal
from Orders
group by country
order by 2 desc

Results:

The resulting chart:

Example 2: Number of orders per country

SQL query:

select Country, count(total) as CTotal
from Orders
group by country
order by 2 desc

Results:

The resulting chart:

Example 3: Shaping your data in a more complex way

This example shows how to use GROUP BY in conjunction with INNER JOIN. For example, we have the following data and would like to display a diagram illustrating how many flags each client has.

 

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

Results:

The resulting chart:

See also:

•Interactive SQL tutorial

•Creating charts

•A list of chart types

•Chart parameters

•ChartModify event

•SQL query screen

•Building a connected scatter chart