soneweng soneweng - 7 months ago 11
SQL Question

Why does the SQL return different result while using count and count,* in query

I have tables

attendance
,
employee
,
payroll


Attendance table:

Employee_ID Clockin Clockout
---------------------------------
1 08:00:00 18:00:00
2 08:00:00 18:00:00
3 08:00:00 18:00:00
4 08:00:00 18:00:00


Employee table:

Employee_ID Name Employee_type
---------------------------------
1 Mary Full-time
2 Peter Full-time
3 John Full-time
4 Henry Full-time


This is the SQL query that I get only 1 row

SELECT *, COUNT(attendance.Clockin)
FROM payroll
LEFT JOIN attendance ON attendance.Employee_ID = payroll.Employee_ID
LEFT JOIN employee ON employee.Employee_ID = payroll.Employee_ID
WHERE employee.Employee_type = 'Full-time'


Here is the SQL query that I get more rows returned

SELECT *
FROM payroll
LEFT JOIN attendance ON attendance.Employee_ID = payroll.Employee_ID
LEFT JOIN employee ON employee.Employee_ID = payroll.Employee_ID
WHERE employee.Employee_type = 'Full-time'


What's the difference between these two queries?

Answer

SQL FIDDLE

The above fiddle has 3 different queries; I extended your 1st query to show what happens in mysql when you don't have a group by.

Consider:

--Yours without a group by, you get one row.  This is due to mySQL "Extended" group by  
SELECT *, COUNT(attendance.Clockin)
FROM payroll
LEFT JOIN attendance
ON attendance.Employee_ID = payroll.Employee_ID
LEFT JOIN employee
ON employee.Employee_ID = payroll.Employee_ID
WHERE employee.Employee_type = 'Full-time';

--Mine, with a group by, you get multiple rows
SELECT *, COUNT(attendance.Clockin)
FROM payroll P
LEFT JOIN attendance
ON attendance.Employee_ID = P.Employee_ID
LEFT JOIN employee
ON employee.Employee_ID = P.Employee_ID
WHERE employee.Employee_type = 'Full-time'
GROUP BY P.Employee_ID, clockin, clockout, employee_Type;

SELECT *
FROM payroll
LEFT JOIN attendance
ON attendance.Employee_ID = payroll.Employee_ID
LEFT JOIN employee
ON employee.Employee_ID = payroll.Employee_ID
WHERE employee.Employee_type = 'Full-time';

Simply put if you don't define the group by the mySQL DB engine will pick a single value from each column and return it. Since the first SQL statmeent has an aggregate, the extended group by is in effect; whereas the second SQL has no aggregate so it simply returns every row. From mySQL docs: The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Comments