phpmyadmin Question posted in
The official documentation can be found here.

SQL Query count row of same id in a different table – Phpmyadmin

Already checked other answers but unfortunately didn’t help me. So im hoping to count how many same id from different table. This is how my tables look like

enter image description here

The first table from the left is “resources” and the second is “equipment_entity”. So I want to count how many resource_class_id is there when joined with equipment_entity.id. This is what I’ve tried so far but doesn’t work. Any help would do. Link with same problem or any help. Thanks ahead!

SELECT e.*, r.equipment_class_id, r.resource_type FROM equipment_entity as e LEFT JOIN resources as r ON e.id = r.equipment_class_id COUNT(DISTINCT r.equipment_class_id) where r.resource_type = 'equipment'

and also, im having this error

1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
near ‘COUNT(DISTINCT r.equipment_class_id) where r.resource_type =
‘equipment’ LIMIT 0′ at line 1

Tags:

4

Answers


  1. 0

    Try following query. This will give you all the Rows from equipment_entity Table and the count of rows in resource table for each equipment.

    SELECT e.*, count(*) AS count FROM `equipment_entity` AS e left join `resources` AS r ON e.id = r.equipment_class_id GROUP BY e.id
    Login or Signup to reply.
  2. 0

    Use this

    SELECT r.equipment_class_id, r.resource_type, count(r.equipment_class_id) count 
FROM equipment_entity as e LEFT JOIN resources as r 
ON e.id = r.equipment_class_id  where r.resource_type = 'equipment' GROUP BY equipment_class_id
    Login or Signup to reply.
  3. 0

    There are two options. You you are decided that you wants to know the count for r.resource_type = ‘equipment’, then the first query will work for you. 

    SELECT COUNT(DISTINCT r.equipment_class_id)
FROM equipment_entity AS e
     LEFT JOIN resources AS r ON e.id = r.equipment_class_id
WHERE r.resource_type = 'equipment';

    But if you wants to see all resource_type’s count in one go, you can use GROUP BY as below to get your desired results.

    SELECT r.equipment_class_id,
COUNT(DISTINCT r.equipment_class_id)
FROM equipment_entity AS e
     LEFT JOIN resources AS r ON e.id = r.equipment_class_id
GROUP BY r.equipment_class_id;
    Login or Signup to reply.
  4. 0

    So I want to count how many resource_class_id is there when joined with equipment_entity.id

    I don’t quite follow that this means. Two interpretations are below.

    Based on your data model, the table resources seems to have at least two columns id and resource_class. The table equipment_entity seems to have resource_class_id. I have adjusted this answer to reflect these names.

    If you want to count the number of equipment that have a particular resource type, then just use JOIN and COUNT():

    SELECT COUNT(*)
FROM equipment_entity  e JOIN
     resources r
     ON r.id = e.resource_class_id
WHERE r.resource_class = 'equipment';

    Neither COUNT(DISTINCT) nor a LEFT JOIN are needed.

    If you want to count all resource classes, then:

    SELECT r.resource_class, COUNT(e.id)
FROM resources r JOIN
     equipment_entity e
     ON r.id = e.resource_class_id
GROUP BY r.resource_class;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search