|
Sql statement for multiple lines in chart 13 Years ago
|
Karma: 0
|
Hello,
I want to have a multi line chart like the one on your demo site.
The data is something like this
Swimstroke Date Time
1 01-01-2011 1,30
1 01-02-2011 1,25
1 01-03-2011 1.26
3 01-01-2011 1,48
3 01-02-2011 1,46
3 01-03-2011 1.50
The Strokes are the lines i want to see in the chart. The date is the x-axe and the time is the y-axe.
The SQL Statement i am using is like this
| Code: |
Select MeetsDate, TotalTime, Zwemslag from Results
WHERE Results.MembersId=347
ORDER BY MeetsDate
|
But i get only one line in the chart but there are 4 swimstrokes in total to show.
Best regards
Robetrt Helthuis |
|
|
|
|
|
|
Re:Sql statement for multiple lines in chart 13 Years ago
|
Karma: 760
|
Hello,
Try to set "SQL -> Revert data" parameter to "Yes". If it doesn't help, could you provide a link to a page where we can see chart and temporary access to your J! backend by email so we can investigate the issue?
Regards,
ARI Soft |
|
|
|
|
|
|
Re:Sql statement for multiple lines in chart 13 Years ago
|
Karma: 0
|
|
Hello,
I succeeded to make the Mysql query so that the data was in the form the chart needed. The magic word was "GROUP_CONCAT".
The problem i am now facing is that if a dataItem has a "NULL" value, The Chart shows a gap in the line (Gchart).
Do you have a solution for this.
Dear regards
Robert Helthuis
|
|
|
|
|
|
|
Re:Sql statement for multiple lines in chart 13 Years ago
|
Karma: 760
|
|
Hello,
Could you provide a link to a page where we can see a chart and specify what SQL query you use?
Regards,
ARI Soft
|
|
|
|
|
|
|
Re:Sql statement for multiple lines in chart 13 Years ago
|
Karma: 0
|
The chart is on joomla.helthuis.net.
The datatable which is using is shown beneeth the chart.
The SQL statement is:
| Code: |
select `Meets`.`MeetsDate` AS `MeetsDate`,
group_concat(if((`Swimstyle`.`Stroke` =1),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'') separator'') AS `Vrij`,
group_concat(if((`Swimstyle`.`Stroke` =2),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'') separator'') AS `Rug`,
group_concat(if((`Swimstyle`.`Stroke` =3),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'') separator'') AS `Vlinder`,
group_concat(if((`Swimstyle`.`Stroke` =4),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'') separator'') AS `Wissel`
from ((`Results` join `Meets` on((`Results`.`MeetsId` =`Meets`.`MeetsId`))) join `Swimstyle` on((`Swimstyle`.`SwimstyleId` =
`Results`.`StyleId`))) where ((`Results`.`MembersId` = 347) and (`Swimstyle`.`Distance`= 50)) group by `Meets`.`MeetsDate`
order By MeetsDate DESc limit 25
|
|
|
|
|
|
|
|
Re:Sql statement for multiple lines in chart 13 Years ago
|
Karma: 760
|
It is possible to replace empty values with 0 value. In this case SQL query will look like:
| Code: |
select `Meets`.`MeetsDate` AS `MeetsDate`,
group_concat(if((`Swimstyle`.`Stroke` =1),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'0') separator'') AS `Vrij`,
group_concat(if((`Swimstyle`.`Stroke` =2),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'0') separator'') AS `Rug`,
group_concat(if((`Swimstyle`.`Stroke` =3),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'0') separator'') AS `Vlinder`,
group_concat(if((`Swimstyle`.`Stroke` =4),replace(replace(`Results`.`TotalTime`,':',''),',','.'),'0') separator'') AS `Wissel`
from ((`Results` join `Meets` on((`Results`.`MeetsId` =`Meets`.`MeetsId`))) join `Swimstyle` on((`Swimstyle`.`SwimstyleId` =
`Results`.`StyleId`))) where ((`Results`.`MembersId` = 347) and (`Swimstyle`.`Distance`= 50)) group by `Meets`.`MeetsDate`
order By MeetsDate DESc limit 25
|
Regards,
ARI Soft |
|
|
|
|
|
|
