CODEWITHSUNDEEP
mysqlsqlmysql-5.6
Vincent Mallet
Vincent Mallet

Reputation: 69

Complex mysql query counting

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!

Upvotes: 0

Views: 54

Answers (2)

Jethan
Jethan

Reputation: 71

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'

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 2

Related Questions