count multiple columns using mysql and php

Question

I have a sales table with the below columns

name
type (student, adult, worker)
sales (A,B,C,D)
cost
location (1,2,3,4)

i want to get the count of type, sales and location which is relevant to the name and group by the name so i can display it on a table enter image description here

is it possible get all the grouped values using a query>

any help willbe appreciated..

Please post the query that doesn't work and we can help tweak it. — HappyTimeGopher
– HappyTimeGopher, Commented May 28, 2012 at 11:42

eggyal · Accepted Answer · 2012-05-28 11:42:54Z

1

Use GROUP BY and then SUM(condition) to obtain your counts:

SELECT   name AS Name,
         SUM(type     = 'worker' ) AS Worker,
         SUM(type     = 'student') AS Student,
         SUM(type     = 'adult'  ) AS Adult,
         SUM(sales    = 'A'      ) AS A,
         SUM(sales    = 'B'      ) AS B,
         SUM(sales    = 'C'      ) AS C,
         SUM(sales    = 'D'      ) AS D,
         SUM(location = 1        ) AS Location1,
         SUM(location = 2        ) AS Location2,
         SUM(location = 3        ) AS Location3,
         SUM(location = 4        ) AS Location4
FROM     sales
GROUP BY Name

answered May 28, 2012 at 11:42

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LiveEn Over a year ago

the columns aren’t numeric so I can’t use SUM, I need to get the count of those columns

eggyal Over a year ago

@LiveEn: Whilst the columns are not numeric, the results of the comparison operations are (true is 1, false is 0); therefore SUM gives a count of matching columns, as desired.

Shiju S S · Accepted Answer · 2013-08-13 15:05:08Z

function getDistributorStat() // One Distributor
{

    $this ->db->select("count(*) ch1 ,
                        sum(c_privi_flag = 'Y') ch2, sum(c_happy1_flag = 'Y') ch3, sum(c_happy2_flag = 'Y') ch4,
                        sum(date(D_JOIN) = CURRENT_DATE) nch1,
                        sum(date(d_privi_activated) = CURRENT_DATE) nch2,
                        sum(date(d_happy1_activated) = CURRENT_DATE) nch3,
                        sum(date(d_happy2_activated) = CURRENT_DATE) nch4"
                        );
    $this->db->from('bc_master');
    $query = $this ->db->get()->row();
    $data['distributors']=$query->ch1;
    $data['privilaged']=$query->ch2;
    $data['happy1']=$query->ch3;
    $data['happy2']=$query->ch4;
    $data['new_distributors']=$query->nch1;
    $data['new_privilaged']=$query->nch1;
    $data['new_happy1']=$query->nch1;
    $data['new_happy2']=$query->nch1;

    return $data;

}

Collectives™ on Stack Overflow

count multiple columns using mysql and php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related