Visualisation difficulties

[gstyles]

Good day all,
I have a database table with electrical test data in it, 2 of the fields are date_of_test and test_frequency (3 values 3,6,12). I should like to be able to display to the customer a graphic image (Chart or pie chart) of how many items are out of test i.e. 21 x 3 monthly 12 x 6 monthly and 4 x 12 monthly.
I have absolutely no idea how to achieve this, I watched the old visualisation demo and am still baffled. If some kind soul could point me in the direction I should be grateful. Thanks gws.

 

[cheesegrits]

I would probably do this with a MySQL view, to build a single row view with your sums in them. You can then introduce this to Fabrik as if it was a "normal" table for a new List, and build a Fusion Chart viz for that List.

Your view query would look something like this:

CREATE VIEW your_view_name AS SELECT id, (SELECT COUNT(*) FROM yourtable WHERE test_frequency = '3' AND test_date < SUBDATE(NOW(), INTERVAL 3 month) AS tot_3, (SELECT COUNT(*) FROM yourtable WHERE test_frequency = '6' AND test_date < SUBDATE(NOW(), INTERVAL 6 month) AS tot_6, (SELECT COUNT(*) FROM yourtable WHERE test_frequency = '12' AND test_date < SUBDATE(NOW(), INTERVAL 12 month) AS tot_12;

This should yield a view with a single row, which has a "dummy" id field, and three totals, tot_3, tot_6 and tot_12.

You can test it by just running the query part (everything from the first SELECT onwards) in phpMyAdmin as an SQL query, and making sure it does what you expect. Then just prepend the "CREATE VIEW your_view_name AS " in front and run it again, which will create the actual view.

Then you can create a Fabrik List, and point it at your_view_name.

You can then create a Fusion Charts viz, select that new List, and create a Pie chart.

Obviously change the view, table and field names in the above query to match your requirements.

-- hugh

 

[gstyles]

Hi Hugh, and thank you very much for the pointers.
This is the query I used from your example:-
SELECT id, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '3' AND date_of_test < SUBDATE(NOW(), INTERVAL 3 MONTH) AS tot_3, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '6' AND date_of_test < SUBDATE(NOW(), INTERVAL 6 MONTH) AS tot_6, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '12' AND date_of_test < SUBDATE(NOW(), INTERVAL 12 MONTH) AS tot_12;

And this is the error I get :-
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS tot_3, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '6' AND date_of_test' at line 1

 

[troester]

I think there's alway one ) missing after MONTH (to close the Sub-SELECT):
SELECT id, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '3' AND date_of_test < SUBDATE(NOW(), INTERVAL 3 MONTH)) AS tot_3, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '6' AND date_of_test < SUBDATE(NOW(), INTERVAL 6 MONTH)) AS tot_6, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '12' AND date_of_test < SUBDATE(NOW(), INTERVAL 12 MONTH)) AS tot_12;

 

[gstyles]

Thank you Troester that has almost worked. I now only have one little error:-
Query: SELECT id, (SELECT COUNT(*) FROM gypsum WHERE test_frequency = '3' AND date_of_test < SUBDATE(NOW(), INTERVAL 3 MONTH)) AS tot_3...

Error Code: 1054
Unknown column 'id' in 'field list'
Which is quite odd as there definitely is a column called id.
thanks gws.

 

[gstyles]

Cracked the query part, the id needed quotes 'id' Many thanks to Hugh and troester for your help,now lets see if I can create a visualisation...

 

[gstyles]

Thank you very much gents, the visualisation is working a treat.