2. PHPRunner General questions
  3. Preparing data for a chart
This topic is locked

Preparing data for a chart

 		7/28/2008 4:51:39 AM
PHPRunner General questions
T
 thesofa author

Hi

I have a master table, Pupils

one of the fields is Gender, obviously set as either M or F as I am English.
The detail table, called Details, is linked to the Pupil_id field from the Pupils table.

I need to have some sets of totals from the Details table, in the form of Count() and Sum() from certain fields. BUT, I need the results split by Gender. so when I come to graph the results, I have the data split into the sexes.

I have managed this in the past, by creating 2 views of the data, one for each sex, and then pulling all the data together with joins in a third sql query to get the data ready.

I just feel it would be nice to be able to pull the data from one query, possibly using subqueries?

Logic says it will be faster too.

I would love some help on this on please!

Cheers.

P.S. It aint half hot in England at the moment, I just wish I was on holiday.....
J
 		Jane 7/28/2008

Hi,
It's difficult to tell you what's happening without seeing actual queries.

In general you can use GROUP BY clause to calculate your counts and sums by gender.
T
 		thesofa author 7/28/2008

Hi,

It's difficult to tell you what's happening without seeing actual queries.

In general you can use GROUP BY clause to calculate your counts and sums by gender.



That works fine, it splits the data, but how do I get different titles for the data columns in the chart?

I have the data grouped by reg group, then by gender, this is lovely, when I graph it, I just have the Tutor group heading as the title for the column, i cannot distinguish between male and female.

The SQL query is as follows

SELECT

nd_pupils.reg AS `Tutor Group`,

SUM(nd_token_reason.weight) AS `Group Total`,

Concat(left(staff.s_firstname, 1), ". ", staff.s_lastname) AS Teacher,

nd_service.datestamp AS `Date Awarded`,

nd_pupils.gender

FROM nd_service

INNER JOIN staff ON nd_service.staff_id = staff.id_staff

INNER JOIN nd_pupils ON nd_service.pup_id = nd_pupils.id_pup

INNER JOIN nd_token_reason ON nd_service.reason = nd_token_reason.id_token_reason

GROUP BY nd_pupils.gender, nd_pupils.reg



This produces 2 entries in the chart per tutor group, male then female, but I cannot distinguish between the two, can I have one colour for male and one for female?
If I change the last line of the query to this

GROUP BY nd_pupils.reg, nd_pupils.gender



Then I get all the female entries then all the male entries, then sorted by tutor group, but again I cannnot have the headings.
OK, I have played a bit more and I have managed to get to the stage where I can have a concat statement to meld the tutorgroup and the gender as the column title, but I would still like one colour for male and another for female is poss, can i do this?
here is the new query

SELECT

SUM(nd_token_reason.weight) AS `Group Total`,

Concat(left(staff.s_firstname, 1), ". ", staff.s_lastname) AS Teacher,

nd_service.datestamp AS `Date Awarded`,

Concat(nd_pupils.reg, " ", nd_pupils.gender) AS `Group by Gender`,

nd_pupils.reg

FROM nd_service

INNER JOIN staff ON nd_service.staff_id = staff.id_staff

INNER JOIN nd_pupils ON nd_service.pup_id = nd_pupils.id_pup

INNER JOIN nd_token_reason ON nd_service.reason = nd_token_reason.id_token_reason

GROUP BY Concat(nd_pupils.reg, " ", nd_pupils.gender), nd_pupils.reg
T
 		thesofa author 7/29/2008

Hi, I have edited this post but it does not "bump" it so plz have another look at my questions, thanks for all your help.
G
J
 		Jane 7/29/2008

Have you checked off Multicolor option on the Chart appearance tab?
T
 		thesofa author 7/29/2008

Yes I have, with a tick in multicolour, i get all the columns, up to 70 of them, in differing colours, with it unticked I get just one shade.

I would like to get all the Male data blue and all the female data Pink.

If Possible please.

Thanks, G
J
 		Jane 7/29/2008

Unfortunately you can't select color for each record (column).
T
 		thesofa author 7/30/2008

OK, thanks, looks like I shall just have to live with it, is there any possibility of this being possible in the future?
J
 		Jane 7/30/2008

Hi,
we plan to use new version of AnyChart in the next PHPRunner version.