CODEWITHSUNDEEP
mysqlsql
JimRomeFan
JimRomeFan

Reputation: 413

Query giving undesirable results

I have the following two tables:

lab_assistant
la_id - la_firstname - la_lastname
1 - Dennis - Rodman
2 - Michael - Jordan
3 - Horace - Grant

hours_worked
hw_semester - hw_date - hw_hours - la_id
Fall 2012 - 2012-11-01 - 4 - 2
Fall 2012 - 2012-11-04 - 5 - 3
Spring 2012 - 2012-02-12 - 4 - 1
Spring 2012 -2012-03-10 - 4 - 1

The result of my query is supposed to look like the following:

and I'm trying to run the following query in order to list the Lab Assistant and the number of hours they worked each semester

SELECT DISTINCT(CONCAT(la.la_firstname, ' ', la.la_lastname)) AS 'Lab Assistant',
   hw.hw_semester AS 'Semester Worked', hw.hw_hours AS 'Hours Worked'
FROM lab_assistant la
    JOIN hours_worked hw
        ON la.la_id = hw.la_id;

The results of my quere are SUPPOSED to look like this:

Michael Jordan - Fall 2012 - 4
Horace Grant - Spring 2012 - 5
Dennis Rodman - Fall 2012 - 8

BUT the results I'm getting are as follows:

Michael Jordan - Fall 2012 - 4
Horace Grant - Fall 2012 - 5
Dennis Rodman - Spring 2012 - 4

So basically, it just isn't counting the proper hours for Dennis Rodman. It should be 8 and not 4.

And I probably shouldn't use DISTINCT here because it's possible the same person could work different semesters.

Upvotes: 0

Views: 61

Answers (3)

Taryn
Taryn

Reputation: 247680

DISTINCT needs to go at the beginning of the SELECT:

SELECT DISTINCT CONCAT(la.la_firstname, ' ', la.la_lastname) AS 'Lab Assistant',
    hw.hw_semester, COUNT(w.hw_hours)
FROM lab_assistant la
    JOIN hours_worked hw
        ON la.la_id = hw.la_id;

But since you are using aggregate functions, you will want to use GROUP BY:

SELECT CONCAT(la.la_firstname, ' ', la.la_lastname) AS 'Lab Assistant',
    hw.hw_semester, COUNT(w.hw_hours)
FROM lab_assistant la
    JOIN hours_worked hw
        ON la.la_id = hw.la_id
GROUP BY hw.hw_semester;

Based on your edit, you need something like this:

SELECT CONCAT(la.la_firstname, ' ', la.la_lastname) AS 'Lab Assistant',
  hw.hw_semester AS 'Semester Worked', 
  sum(hw.hw_hours) AS 'Hours Worked'
FROM lab_assistant la
JOIN hours_worked hw
  ON la.la_id = hw.la_id
GROUP BY la.la_id, hw.hw_semester;

See SQL Fiddle with Demo

Results:

|  LAB ASSISTANT | SEMESTER WORKED | HOURS WORKED |
---------------------------------------------------
|  Dennis Rodman |     Spring 2012 |            8 |
| Michael Jordan |       Fall 2012 |            4 |
|   Horace Grant |       Fall 2012 |            5 |

Upvotes: 2

Brendan Long
Brendan Long

Reputation: 54242

The MySQL syntax for SELECT shows that DISTINCT needs to come before the column names:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [ .. etc .. ]
select_expr [, select_expr ...]

Each select_expr indicates a column that you want to retrieve.

Basically, DISTINCT can only apply to the entire query, not a single column.

Upvotes: 0

tadman
tadman

Reputation: 211580

This is because DISTINCT is a modifier to SELECT and cannot be used like that. What's best in your case is to apply a GROUP BY on hw.hw_semester to fix it.

Upvotes: 1

Related Questions