CODEWITHSUNDEEP
mysqlaggregate-functionsdistinct-values
Eugeen
Eugeen

Reputation: 51

Retrieving distinct values in rows and counting total

just looking for any info on my problem, not necessarily the exact answer.

I have a table called WorkDay.

In the Workday Table, there are 6 fields: 

Date_of_Workday, Workday_type, Boss, Regular, Tech1, Tech2.

Employees can be assigned multiple roles and there are also a few fields which are NULL.

I want to write a query that will return the total number of employees assigned to each workday, but obviously if there is an employee assigned to the Boss role and also to the Tech2 role, that will only count as one employee.

EDIT: This is my desired output.

+------------+------------+----+
|Date_of_Work| Work_type  |    |
+------------+------------+----+
| 2019-02-09 | AM         |  4 |
| 2019-02-09 | PM         |  4 |
| 2019-02-10 | AM         |  3 |
| 2019-02-10 | PM         |  2 |
| 2019-02-11 | AM         |  4 |
| 2019-02-11 | PM         |  4 |
| 2019-02-12 | AM         |  4 |
| 2019-02-12 | PM         |  4 |
| 2019-02-13 | AM         |  4 |
| 2019-02-13 | PM         |  4 |
 ------------+------------+----

This is SELECT * FROM Workday:

+------------+------------+---------+----------+-----------+-----------+
| DateofWork | workday_typ | Boss   | Regular  |   Tech1   |   Tech2   |
+------------+------------+---------+----------+-----------+-----------+
| 2019-02-09 | AM         | LB1     | AW1      | AE1       | JE1       |
| 2019-02-09 | PM         | AE1     | IM1      | AL1       | BJ1       |
| 2019-02-10 | AM         | AE1     | MM1      | MW1       | NULL      |
| 2019-02-10 | PM         | AE1     | AE1      | EB1       | NULL      |
| 2019-02-11 | AM         | LB1     | AB1      | DJ1       | JP1       |
| 2019-02-11 | PM         | LB1     | JE1      | AB2       | BJ1       |
| 2019-02-12 | AM         | LB1     | NS1      | AE1       | MB1       |
| 2019-02-12 | PM         | LB1     | HP1      | EH1       | AL1       |
| 2019-02-13 | AM         | LB1     | EB2      | ME1       | MM1       |
| 2019-02-13 | PM         | LB1     | ME2      | AB2       | DJ1       |
+------------+------------+---------+----------+-----------+-----------+

I am unsure as to go about this query. Any information/pointers would be greatly appreciated.

Upvotes: 0

Views: 497

Answers (1)

O. Jones
O. Jones

Reputation: 108776

It looks like your woefully denormalized table has mutiple columns containing peoples's names. You want to count the number of unique values in all these columns for each day, it seems.

Try something like this to get the unique values on each day. This gives back a result set with Date_of_Workday, Workday_Type, Person as columns. The use of UNION eliminates duplicate Person values.

          SELECT Date_of_Workday, Workday_Type, Boss AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type,  Regular AS Person  FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech1 AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech2 AS Person FROM tbl

Then use that subquery in an aggregate query like this: (http://sqlfiddle.com/#!9/f1320a/1/0)

SELECT Date_of_Workday, Workday_Type, COUNT(Person) AS PersonCount
  FROM (
          SELECT Date_of_Workday, Workday_Type, Boss AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type,  Regular AS Person  FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech1 AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech2 AS Person FROM tbl
       ) Persons
 GROUP BY Date_of_Workday, Workday_Type

You need COUNT(Person) instead of COUNT(*) here, otherwise it will count NULL values and therefore overcount people.

Upvotes: 1

Related Questions