charting by gender

[biosol]

I've got a chart showing the number (count) of people by age working, but I'm now trying to get it to display the number of people of each age group by gender.

I have created a query for each gender and it basically works except that it charts the bars for the first series (female) and then starts over charting the bars for the second series (male) instead of integrating them. I suspect this is because it's from two query's but am not sure.

Can someone steer me in the right direction? Thanks,

Current Query's
--------------------
<cfquery name="getfemaleages" datasource="">
SELECT age_group, count(age_group) AS count
FROM client_info
WHERE gender = 'female'
GROUP BY age_group
</cfquery>

<cfquery name="getmaleages" datasource="">
SELECT age_group, count(age_group) AS count
FROM client_info
WHERE gender = 'male'
GROUP BY age_group
</cfquery>

Current Chart Code:
---------------------
<cfchart chartwidth="200" showBorder = "no" format="png" title = "Age Doesn't Matter" show3d = "yes" showlegend="yes" backgroundColor="##f8f8f8" xaxistitle="Age Group">

<cfchartseries
seriesColor="##0092CE"
paintStyle="light"
type="Bar"
query="getmaleages"
itemcolumn="age_group"
valuecolumn="count"
serieslabel="Males"
>
<cfchartseries
seriesColor="Red"
paintStyle="light"
type="Bar"
query="getfemaleages"
itemcolumn="age_group"
valuecolumn="count"
serieslabel="Females"
>
</cfchart>

 

[FALCONSEYE]

why not write a single query ?

<cfquery name="getAges" datasource="">
SELECT age_group,
(case when gender = 'female' then count(1) end) as femaleCount,
(case when gender = 'male' then count(1) end) as maleCount,

FROM client_info
GROUP BY age_group
</cfquery>

hope it helps...

http://www.jetansolutions.com

 

[biosol]

that seems to work, however when I chart it, the age groups aren't placed in the right pace. For example the Mens 30-39 column is out of place maybe next to the Womens 50-59 and others. Is there a better way to put it in the chartseries tag?

Currently:
-------------------
<cfchartseries
seriesColor="##0092CE"
paintStyle="light"
type="Bar"
query="getmaleages"
itemcolumn="age_group"
valuecolumn="count"
serieslabel="Males"
>
<cfchartseries
seriesColor="Red"
paintStyle="light"
type="Bar"
query="getfemaleages"
itemcolumn="age_group"
valuecolumn="count"
serieslabel="Females"
>