CODEWITHSUNDEEP
mysqlsql
freddy
freddy

Reputation: 155

mysql count distinct value

I have trouble wondering how do I count distinct value. using if on the select column

I have SQLFIDDLE here

http://sqlfiddle.com/#!2/6bfb9/3

Records shows:

create table team_record (
  id tinyint,
  project_id int,
  position varchar(45)
  );

insert into team_record values
(1,1, 'Junior1'),
(2,1, 'Junior1'),
(3,1, 'Junior2'),
(4,1, 'Junior3'),
(5,1, 'Senior1'),
(6,1, 'Senior1'),
(8,1, 'Senior2'),
(9,1, 'Senior2'),
(10,1,'Senior3'),
(11,1, 'Senior3'),
(12,1, 'Senior3')

I need to count all distinct value, between Junior and Senior column.

all same value would count as 1.

I need to see result something like this.

PROJECT_ID  SENIOR_TOTAL    JUNIOR_TOTAL
1                3              3

mysql query is this. but this is not a query to get the result above.

SELECT 
    `team_record`.`project_id`,
    `position`,       
    SUM(IF(position LIKE 'Senior%',
        1,
        0)) AS `Senior_Total`,
    SUM(IF(position LIKE 'Junior%',
        1,
        0)) AS `Junior_Total`
FROM
    (`team_record`)        
WHERE
    project_id = '1'        
GROUP BY `team_record`.`project_id`

maybe you could help me fix my query above to get the result I need.

thanks

Upvotes: 0

Views: 117

Answers (1)

Bohemian
Bohemian

Reputation: 425398

I think you want this:

SELECT 
   project_id,
   COUNT(DISTINCT CASE when position LIKE 'Senior%' THEN position END) Senior_Total,
   COUNT(DISTINCT CASE when position LIKE 'Junior%' THEN position END) Junior_Total
FROM team_record
WHERE project_id = 1
GROUP BY project_id

The CASE will return a null if the WHEN is false (ie ELSE NULL is the default, which I omitted for brevity), and nulls aren't counted in DISTINCT.

Also, unnecessary back ticks, brackets and qualification removed.

Upvotes: 6

Related Questions