Complex mysql query counting

Question

I have 1 table name "companies" with several datas like :

Id	Owner	Company	Job
1	John Doe	Company 1	CEO
1	John Doe	Company 2	CEO
1	John Doe	Company 3	CEO
1	Gab Durand	Company 4	CEO
1	Rob Dujn	Company 5	CTO
1	Alex DoeMorse	Company 6	COO

What I need is to get 1 line by company with a row calculating the number of company own by each person.

This is my desired output :

Id	Owner	Company	Job	Count
1	John Doe	Company 1	CEO	3
1	John Doe	Company 2	CEO	3
1	John Doe	Company 3	CEO	3
1	Gab Durand	Company 4	CEO	1
1	Rob Dujn	Company 5	CTO	1
1	Alex DoeMorse	Company 6	COO	1

What could be the mysql query?

EDIT : DB version 5.6.51

Thanks!

Please share more details. If you are using MySQL, then you are most probably not using v7, as this does not exist — Nico Haase
– Nico Haase, Commented Jun 25, 2021 at 11:47

Barbaros Özhan · Accepted Answer · 2021-06-25 12:18:34Z

2

You can add an extra column containing analytic function such as

COUNT(*) OVER (PARTITION BY Id, owner) AS count

if DB version is 8.0

As having a former DB version, prefer using correlated subquery such as

SELECT Id, Owner, company, Job,
       (SELECT COUNT(*) 
          FROM t 
         WHERE id = tt.id 
           AND Owner = tt.Owner ) AS count
  FROM t AS tt

Demo

edited Jun 25, 2021 at 12:18

answered Jun 25, 2021 at 11:34

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Jethan · Accepted Answer · 2021-06-25 12:20:45Z

0

Query:

SELECT Owner, Job, count(Company) NoOfCompanies 
FROM   companies 
WHERE  Job='CEO' 
GROUP BY Owner,Job;

Note: Grouping done on Owner and Job expecting you have other values along with 'CEO'

answered Jun 25, 2021 at 12:20

Jethan

715 bronze badges

Collectives™ on Stack Overflow

Complex mysql query counting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related