mysql count distinct value

Question

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

I have SQLFIDDLE here

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

This would be much simpler (and faster) if you separated the numeric component out to another column — Strawberry
– Strawberry, Commented Sep 3, 2014 at 7:12

Bohemian · Accepted Answer · 2014-09-03 07:09:07Z

6

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.

edited Sep 3, 2014 at 7:09

answered Sep 3, 2014 at 7:02

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Collectives™ on Stack Overflow

mysql count distinct value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related