MySQL count row

Question

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

If my answer solved your problem, you should click the checkbox next to the answer to mark it as the "accepted" answer. — chaos
– chaos, Commented Aug 24, 2009 at 3:39

chaos · Accepted Answer · 2009-08-24 02:35:30Z

7

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.

edited Aug 24, 2009 at 2:35

answered Aug 24, 2009 at 2:23

chaos

125k34 gold badges306 silver badges310 bronze badges

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

2 Comments

too much php Over a year ago

You will probably need to use table_a.ID_COMPANY in the select clause.

Daniel Budihardja Over a year ago

tq! it solved.. actually i want to get how many user accounts for each registered companies at my site. thx=)

Matthew Iselin · Accepted Answer · 2009-08-24 02:20:41Z

2

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.

answered Aug 24, 2009 at 2:20

Matthew Iselin

10.7k4 gold badges54 silver badges62 bronze badges

Comments

corymathews · Accepted Answer · 2009-08-24 02:32:46Z

1

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

answered Aug 24, 2009 at 2:32

corymathews

12.6k14 gold badges60 silver badges77 bronze badges

Comments

Rizon · Accepted Answer · 2009-08-24 02:30:04Z

0

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.

answered Aug 24, 2009 at 2:30

Rizon

12910 bronze badges

Comments

pilcrow · Accepted Answer · 2009-08-24 02:43:42Z

... 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)

Collectives™ on Stack Overflow

MySQL count row

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related