CODEWITHSUNDEEP
phpmysqlcodeigniter
Rajan
Rajan

Reputation: 2425

How to get count of a field in another table codeigniter using group by?

I have a table called sitekeys:

sitekeys

In this site_key contains a unique license. And The field called licenses contains the no of times it can be used. the org_id field contains the company to which it has been assigned.

Now the activation table:

activation

in this table site_key contains the keys assigned to the device.

Now I want to count The keys used for a company.

Like for XYZ company i has give 10 licenes then i want to know how many of them are used by it, so for that i can count(site_key) in activation table.

So what should be my query here?

I think i wil have to use Group_BY to do so.

I want show the keys i gave to a company and the keys they have used

Upvotes: 1

Views: 118

Answers (2)

Angel
Angel

Reputation: 612

$this->db->select("count(activation.site_key)");
$this->db->from("activation");
$this->db->join("sitekeys");
$this->db->group_by("sitekeys.site_key");
$this->db->get();

Try this

Upvotes: 0

Roby Samuel
Roby Samuel

Reputation: 21

i don't know exactly the design of your table so safely i have to join the two table

SELECT a.org_id, a.licenses, b.used_key from sitekeys a
LEFT JOIN 
(
 SELECT site_key, user_id, count(site_key) as used_key 
 FROM 
 activation b 
 GROUP BY site_key,user_id
) b
on a.site_key = b.site_key

Upvotes: 1

Related Questions