Improve existing coding style?

[alvin567]

$person =  array(1,2,3,4,5,6,7,8,9,10);
       foreach($person as $person){
           $sizeOfCompany = $this->Claim->query("Select count(*),count(*) * 100/(Select count(*) from claims) from claims where size = '$person' ");
                                    # of person,No of firms,% of contributions
           array_push($data,array($person,$sizeofCompany[0][0]['computed'],$sizeofCompany[0][0]['computed1'] . '%'));
       }
        $sizeofCompany = $this->Claim->query("Select count(*),count(*) * 100/(Select count(*) from claims) from claims where size Between 11 and 19");
             array_push($data,array('11-19',$sizeofCompany[0][0]['computed'],$sizeofCompany[0][0]['computed1'] . '%'));
        $sizeofCompany = $this->Claim->query("Select count(*),count(*) * 100/(Select count(*) from claims) from claims where size Between 20 and 40");
             array_push($data,array('20-40',$sizeofCompany[0][0]['computed'],$sizeofCompany[0][0]['computed1'] . '%'));
        $sizeofCompany = $this->Claim->query("Select count(*),count(*) * 100/(Select count(*) from claims) from claims where size > 40");
             array_push($data,array('Over 40',$sizeofCompany[0][0]['computed'],$sizeofCompany[0][0]['computed1'] . '%'));
         array_push($data,'');

 

How can i improve the quality of this code?

I want to find the range 1,2,3,4,5,6,7,8,9,10,11-19,20-40,Over 40

And then i want to calculate the percentage of the total count

[alvin567]

I manage to do this some how

 

Select size,

Case

when size = 1 then COUNT(*)

when size = 2 then COUNT(*)

when size = 3 then COUNT(*)

when size = 4 then COUNT(*)

when size = 5 then COUNT(*)

when size = 6 then COUNT(*)

when size = 7 then COUNT(*)

when size = 8 then COUNT(*)

When size = 9 then COUNT(*)

When size = 10 then COUNT(*)

When size Between 11 and 19 then COUNT(*)

When size Between 20 and 40 then COUNT(*)

End As Count

from  group by size order by size asc;

 

but the size is returns 11 and 19 between all?

[alvin567]

http://www.sitepoint.com/forums/showthread.php?638732-Grouping-into-a-Range-of-Values-in-MS-SQL-2005

 

how can I make sense out of this?

[alvin567]

 

The problem is it is asking for the range of 11-19 of size when I only specifically ask for within the range,

any why how I can resolve this?

[alvin567]

How can it be extended within a certain range?

[trq]

See here

[smoseley]

SELECT 
    size_group,
    COUNT(*) AS quantity
FROM (
    SELECT size, CASE 
            WHEN size <= 10 THEN CAST(size AS CHAR)
            WHEN size BETWEEN 11 AND 19 THEN '11-19'
            WHEN size BETWEEN 20 AND 40 THEN '11-40'
        END AS size_group
    FROM table_name) AS table_alias
GROUP BY size_group
ORDER BY size_group ASC;

[alvin567]

wow

 

this is great! thank you so much!

[alvin567]

However there is an issues,

when 1,10 then 11-19,I expect it to be pushed down to the end.

[alvin567]

This couldn't be solved

 

http://s1071.photobucket.com/albums/u515/flame_copper/?action=view&current=Untitled-1.jpg

[smoseley]

This will fix your ordering issues

SELECT 
    size_group,
    COUNT(*) AS quantity
FROM (
    SELECT size, CASE 
            WHEN size <= 10 THEN CAST(size AS CHAR)
            WHEN size BETWEEN 11 AND 19 THEN '11-19'
            WHEN size BETWEEN 20 AND 40 THEN '11-40'
        END AS size_group
    FROM table_name) AS table_alias
GROUP BY size_group
ORDER BY MAX(size) ASC;