colombo colombo - 4 months ago 15
MySQL Question

Mysql Multiple queries to calculate values for month

first of all i want to extract dates of the relevant month from the database.for that i have used following query and i had generated the following output.

code

SELECT DISTINCT date as tot FROM attendance WHERE date LIKE '%2016-06%'


output

enter image description here

then i need to get the present
employees
for that dates from the database.but when i try that, it gives me total for one date only.how can i over come this.Here is my code.

SELECT DISTINCT date as days, COUNT(DISTINCT employee_id) as tot FROM attendance WHERE in_time != '' AND out_time != '' AND date LIKE '%2016-06%'


enter image description here

Answer

You need to use group by. mysql just returns an arbitrary value when you omit fields from the select list that are not included in aggregate functions. Then you can remove distinct:

SELECT date as days, COUNT(DISTINCT employee_id) as tot 
FROM attendance 
WHERE in_time != '' AND out_time != '' AND date LIKE '%2016-06%'
GROUP BY date

Per comment, what data type is your date field? Assuming it's a date, you could remove like and use year and month:

AND Month(date) = 6 AND Year(date) = 2016