Jump to content
#StayAtHome ×

Archived

This topic is now archived and is closed to further replies.

alvin567

Improve existing coding style?

Recommended Posts

$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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

7308108214_1bb411a12a_b.jpg

 

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?

Share this post


Link to post
Share on other sites
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;

Share this post


Link to post
Share on other sites

However there is an issues,

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

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.