1 Reply Latest reply on Dec 29, 2006 6:31 AM by Newsgroup_User

    CFChart (pie graph) question

    Level 7
      I want to make a pie graph using the sum of all the type of animals in the
      database. All animal types are numeric:

      1 = Dogs
      2 = Cats
      3 = Birds, etc.

      I want to count up all the record of each type and display them in the graph
      by percent, but I cannot figure out how. I.E. If there are 5 dogs and 5
      cats, the chart would be cut in 2 (50%).

      I am using this CFC, but it isn't working for the obvious reason. 2(Cats)
      is twice as big as 1 (Dogs) and so on.

      SELECT DISTICT(type), CASE type
      WHEN 1 THEN 'Dogs'
      WHEN 2 THEN 'Cats'
      WHEN 3 THEN 'Bird'
      WHEN 4 THEN 'Ferret'
      WHEN 5 THEN 'Rabbit'
      WHEN 6 THEN 'Rodent'
      WHEN 7 THEN 'Farm Animals'
      WHEN 8 THEN 'Horses'
      END AS NAME
      FROM pets
      GROUP BY type

      I tried COUNT():

      SELECT COUNT(type), CASE type
      WHEN 1 THEN 'Dogs'
      WHEN 2 THEN 'Cats'
      WHEN 3 THEN 'Bird'
      WHEN 4 THEN 'Ferret'
      WHEN 5 THEN 'Rabbit'
      WHEN 6 THEN 'Rodent'
      WHEN 7 THEN 'Farm Animals'
      WHEN 8 THEN 'Horses'
      END AS NAME
      FROM pets
      GROUP BY type

      But all I get is a solid record for birds, even though there is only 1
      entry.

      Do I have to add the recordcount of each type into seperate <chartseries>?
      How can I modify my SQL statement to give me a count of records per each
      type and display it in the pie cart by percentage.

      Thanks!
      --
      Wally Kolcz
      MyNextPet.org
      Founder / Developer