SQL Distinct based on different colum

user_16007817 0
Viewed 58

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?

mysql sql
2 Answers

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
user_68264790

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
user_160078170
Related