Clever Geek Handbook

MySQL count string

how can i count a string based on its content? Suppose I have a 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 a result like this? 

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

thanks guys

+2


source to share


5 answers 


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 SUM

probably need tweaking because I don't understand what exactly you are trying to achieve there.

+7


source


Use subqueries and count the results from them.

In the incorrect "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 talk about the error, I mean I haven't tried this and I'm just trying to figure out the idea rather than giving you something to just copy and paste.

+2


source


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
+1


source


Expanding on Matthew's answer, I suggest you use UNION and GROUP BY. For example:

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

You need to combine these results to get one set of results.

0


source


... and here a JOIN

:

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 (barring more entries 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)
0


source






More articles:


All Articles