MySQL count row

Question

hi all, how can I count row based on its contents? assumed I have table like this

[table a]

ID_COMPANY   |   NAME
-----------------------------
A1           |   COMPANY A

[table b]

ID_COMPANY    |    USER     |    TYPE
--------------------------------------
A1            |   USER A    |   MANAGER
A1            |   USER B    |   DEPT001
A1            |   USER C    |   CUSTOMR
A1            |   USER D    |   DEPT002
A1            |   USER E    |   CUSTOMR

how can i get the result like this?

ID_COMPANY  |    NAME   |  TOTAL_MANAGER  | TOTAL_STAFF_DEPT  | TOTAL_CUST
----------------------------------------------------------------------------
A1          | COMPANY A |              1  |                2  |          1

thx guys

Answer 1

Use subqueries and count the results from them.

In flawed "psuedo-sql":

select ID_COMPANY, NAME,
count(select * from b where type like "MAN*) as "TOTAL_MANAGER",
count(select * from b where type like "DEPT*") as "TOTAL_STAFF_DEPT",
count(select * from b where type like "CUST*") as "TOTAL_CUST"

When I say flawed, I mean I haven't tried this, and I'm merely trying to get the idea across rather than giving you something to just copy & paste.

Answer 2

SELECT
    `table_a`.`ID_COMPANY`,
    `NAME`,
    SUM(IF(`TYPE` = 'MANAGER', 1, 0)) AS `TOTAL_MANAGER`,
    SUM(IF(`TYPE` LIKE 'DEPT%', 1, 0)) AS `TOTAL_STAFF_DEPT`,
    SUM(IF(`TYPE` = 'CUSTOMR', 1, 0)) AS `TOTAL_CUST`
FROM `table_a`
JOIN `table_b`
USING (`ID_COMPANY`)
GROUP BY `table_a`.`ID_COMPANY`

The criteria for the SUMs will probably need tweaking because I don't understand exactly what you're trying to achieve there.

Answer 3

Expanding on Matthew's reply I would suggest you make use of UNIONs and GROUP BYs. Such as:

SELECT ID_COMPANY, NAME, COUNT(USER) AS TOTAL_MANAGER FROM TABLE_B WHERE TYPE LIKE 'MANAGER' GROUP BY ID_COMPANY

You'll need to union these results to get one resultset.

Answer 4

Something like:

SELECT 
    ID_COMPANY, 
    NAME, 
    (SELECT COUNT(ID_COMPANY) FROM table_b WHERE ID_COMPANY = table_a.ID_Company and TYPE = 'MANAGER') as TOTAL_MANAGER,
    (SELECT COUNT(ID_COMPANY) FROM table_b WHERE ID_COMPANY = table_a.ID_Company and TYPE = 'DEPT001') as DEPT001C,
    (SELECT COUNT(ID_COMPANY) FROM table_b WHERE ID_COMPANY = table_a.ID_Company and TYPE = 'DEPT002') as DEPT002C,
FROM table_a
GROUP BY ID_COMPANY

Answer 5

... and here's a JOIN approach:

SELECT
  a.id_company,
  a.name,
  mgr.cnt  AS total_manager,
  dept.cnt AS total_staff_dept,
  cust.cnt AS total_cust
FROM
  a
  JOIN
  (SELECT id_company, COUNT(*) AS cnt
   FROM b WHERE type = 'MANAGER' GROUP BY id_company)  mgr
    ON a.id_company = mgr.id_company
  JOIN
  (SELECT id_company, COUNT(*) AS cnt
   FROM b WHERE type LIKE 'DEPT%' GROUP BY id_company) dept
    ON a.id_company = dept.id_company
  JOIN
  (SELECT id_company, COUNT(*) AS cnt
   FROM b WHERE type = 'CUSTOMR' GROUP BY id_company)  cust;

... which gives me (assuming no more records than you show):

+------------+------+---------------+------------------+------------+
| id_company | name | total_manager | total_staff_dept | total_cust |
+------------+------+---------------+------------------+------------+
| A1         | foo  |             1 |                2 |          2 |
+------------+------+---------------+------------------+------------+
1 row in set (0.00 sec)