CODEWITHSUNDEEP
mysqlsql
AriffKmy
AriffKmy

Reputation: 11

SQL Distinct based on different colum

I have problem to distinct values on column based on other column. The case study is:

Table: List

well | wbore | op|
------------------
wella|wbore_a|op_a|
wella|wbore_a|op_b|
wella|wbore_a|op_b|
wella|wbore_b|op_c|
wella|wbore_b|op_c|
wellb|wbore_g|op_t|
wellb|wbore_g|op_t|
wellb|wbore_h|op_k|

So, I want the output to be appear in different field/column like:

 well | total_wbore | total_op
    ----------------------------
  wella |      2      |   3
    ---------------------------
  wellb |      2      |   2

the real study case come from different table but to simplify it I just assume this case happened in 1 table.

The sql query that I tried:

SELECT well.well_name, wellbore.wellbore_name, operation.operation_name, COUNT(*) 
FROM well
INNER JOIN wellbore ON wellbore.well_uid = well.well_uid
INNER JOIN operation ON wellbore.well_uid = operation.well_uid 
GROUP BY  well.well_name,wellbore.wellbore_name
HAVING COUNT(*) > 1

But this query is to calculate the duplicate row which not meet the requirement. Anyone can help?

Upvotes: 1

Views: 64

Answers (2)

AriffKmy
AriffKmy

Reputation: 11

Final query:

SELECT 
  well.well_name,
  COUNT(DISTINCT wellbore.wellbore_name) AS total_wbore,
  COUNT(DISTINCT operation.operation_name) AS total_op
FROM well
  INNER JOIN wellbore ON wellbore.well_uid = well.well_uid
  INNER JOIN operation ON wellbore.well_uid = operation.well_uid
  GROUP BY well.well_name

Upvotes: -1

James
James

Reputation: 3015

you need to use count distinct

SELECT 
  count(distinct wellbore.wellbore_name) as total_wbore
  count(distinct operation.operation_name) as total_op
FROM well
  INNER JOIN wellbore ON wellbore.well_uid = well.well_uid
  INNER JOIN operation ON wellbore.well_uid = operation.well_uid

Upvotes: 2

Related Questions