|
|
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:
|
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
SQL query:
select Country, sum(total) as STotal
from Orders
group by country
order by 2 desc
|
Results:
|
USA
|
$560
|
|
Germany
|
$280
|
|
GB
|
$270
|
|
Australia
|
$85
|
|
France
|
$40
|
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:
|
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.
|
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:
|
1001
|
5
|
1
|
2
|
|
1002
|
2
|
1
|
3
|
|
1003
|
1
|
1
|
1
|
Related info:
Interactive
SQL tutorial
|
