How to count based on where in mysql? php

Question

I was wondering how can I make a query that will show the counts that I need using codeigniter?

EX: Submitted to select 90 and 56

$this->db->where('atr_id', 90);
$this->db->or_where('atr_id', 56);

List_table

   id     |    item_id    |   atr_id
---------------------------------------
   1      |        1      |    56
   2      |        1      |    90
   3      |        2      |    56
   4      |        2      |    90
   5      |        2      |    92

The expected return would look like this.

    item_id    |  active_attr   |  total_attr  
----------------------------------------------
        1      |        2       |     2        // active_attr (90,56) and total_attr (90 , 56)
        2      |        2       |     3        // active_attr (90,56) and total_attr (90, 56, 92)

The active_attr counts the number of atr_id found (90, 56) in an item_id

And the total_attr counts the total number of atr_id of an item_id

Need really help here thanks guys...

Could you please clarify how active_attr results in 2 for item_id 2? — Darshan Mehta
– Darshan Mehta, Commented Feb 20, 2017 at 8:52

Haotian Liu · Accepted Answer · 2017-02-20 09:21:43Z

1

I think what you want is GROUP BY, and this SQL query can give your expected return at one time.

SELECT a.item_id, COUNT(DISTINCT a.atr_id) as active_attr, 
COUNT(DISTINCT b.atr_id) as total_attr
FROM test2 a
INNER JOIN test2 b ON a.item_id=b.item_id
WHERE a.atr_id=56 OR a.atr_id=90
GROUP BY a.item_id

And this is the ci query sample.

<?php
    $res = $this->db
              ->select('a.item_id, COUNT(DISTINCT a.atr_id) as active_attr, COUNT(DISTINCT b.atr_id) as total_attr')
              ->join('test2 as b', 'a.item_id=b.item_id')
              ->where('a.atr_id', 56)
              ->or_where('a.atr_id', 90)
              ->group_by('a.item_id')
              ->get('test2 as a')
              ->result();

edited Feb 20, 2017 at 9:21

answered Feb 20, 2017 at 9:09

Haotian Liu

8965 silver badges19 bronze badges

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

Comments

Thomas G · Accepted Answer · 2017-02-20 09:03:14Z

1

You can achieve what you want with this query

SELECT item_id, 
       SUM(
           CASE 
                    WHEN atr_id = 90 THEN 1
                    WHEN atr_id = 56 THEN 1
                    ELSE 0              
                END
            ) AS active_attr, 
            COUNT(*) AS total_atr
FROM List_table
GROUP BY item_id

So you don't need a WHERE clause, the counts of your active_attr is done in the SELECT with the CASE statement. The CASE will return 1 for your submitted values (56 and 90) and 0 for the rest, you then SUM all the 1s to get the count.

edited Feb 20, 2017 at 9:03

answered Feb 20, 2017 at 8:56

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

1 Comment

amy burgess Over a year ago

is there any way this would be in codeigniter format? I tried it in sql line and it works :)

user5279363 · Accepted Answer · 2017-02-20 09:03:54Z

0

did you apply group by method:

 $this->db->group_by('item_id'); 
 $this->db->order_by('item_id', 'desc');

This will produce a result like

|  ITEM_ID |  TOTAL  |
|    12    |    3    |
|    15    |    2    |

answered Feb 20, 2017 at 9:03

user5279363

Comments

fustaki · Accepted Answer · 2017-02-20 09:32:30Z

0

You are looking for the facets (or classification) of the result.

I would use your first query, read the result-set with a loop and populate the two facets (two maps) with a single scan.

$item_facet = array();
$atr_facet = array();

foreach($query->result() as $row){
    $item_id = $row->item_id;
    $atr_id = $row->atr_id;
    $item_facet = increment($item_facet, $item_id);
    $atr_facet = increment($atr_facet, $atr_id);
}

function increment($map, $id){
    if(isset($map[$id])){
        $map[$id] = $map[$id] + 1;
    }else{
        $map[$id] = 1;
    }
    return $map;
}

Alternatively these are two GROUP BY SQL queries

edited Feb 20, 2017 at 9:32

answered Feb 20, 2017 at 8:55

fustaki

1,6141 gold badge14 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to count based on where in mysql? php

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related