I have a big SQLite - Database that contains entries like this:
rowID timestamp employee sold
1 1345294648 myer 1
2 1351508109 miller 2
3 1351508109 smith 8
4 1351508109 miller 10
5 1351508109 smith 1
6 1353243448 myer 10
It represents the amount of units certain employees have sold at a certain time.
With the following code in php i can get the sold units per employee:
<?php
$response = array();
$db = new PDO("sqlite:./db.sqlite");
$query_count = $db -> query("SELECT * FROM myTable GROUP BY employee");
while ($row = $query_count -> fetch(PDO::FETCH_ASSOC)) {
$current_employee = $row['employee'];
$response[$current_employee]["sold"] = $row['sold'];
}
echo json_encode($response);
?>
This will give back the following JSON - formatted data:
{
"miller" : { "sold" : 12 },
"myer" : { "sold" : 11 },
"smith" : { "sold" : 9 }
}
Now i want to separate the sales to the distinct months, like this:
{
"miller":{
"sold":{
"january":1,
"february":8,
"march":9
}
},
"myer":{
"sold":{
"january":10,
"february":11,
"march":13
}
},
"smith":{
"sold":{
"january":11,
"february":10,
"march":10
}
}
}
After that, i want to visualize the data with an bar chart, like this but non-stacked:
Is there a SQL - Request / PHP-Script that can help me?
I'm glad for every answer, thanks in advance.
SQLite delivers different date functions, to aggregate data by dates: